Dan Armbrust wrote:
I'm trying to load some data into PostgreSQL 8.0.3, and I got the
error message "index row size 2904 exceeds btree maximum, 2713".
After a bunch of searching, I believe that I am getting this error
because a value that I am indexing is longer than ~ 1/3 of the block
size - or the BLCKSZ variable in the src/include/pg_config_manual.h file.
Am I correct so far?
I need to fix this problem. I cannot change the indexed columns. I
cannot shorten the data value. And I cannot MD5 it, or any of those
hashing types of solutions that I saw a lot while searching.
Is there a variable I can set somewhere, so that postgresql would just
truncate the value to the max length that the index can handle when it
goes to enter it into the index, instead of failing with an error? I
would be fine with not having this particular row fully indexed, so
long as I could still retrieve the full data value.
The other solution that I saw was to modify the BLCKSZ variable. From
what I saw, it appears that to change that variable, I would need to
dump my databases out, recompile everything, and then reload them
from scratch. Is this correct?
Currently the BLCKSZ variable is set to 8192. What are the
performance/disk usage/other? implications of doubling this value, to
16384?
Any other suggestions in dealing with this problem?
Thanks,
Dan
Thanks for all the information and ideas WRT this issue.
I ended up just having to remove the index from this particular column
that was having the issue - in my particular case, I didn't lose
anything by doing this anyway, because the index wasn't being used for
its intended purpose anyway, due to case sensitivity issues.
Could I suggest adding this error, its causes, and possible solutions
from this thread
(http://archives.postgresql.org/pgsql-general/2005-07/msg00731.php) to
the FAQ? It took me a long time to connect all the dots through a lot
of different e-mail threads.
Also, maybe the max index size should be documented in the manual as well?
Dan
--
****************************
Daniel Armbrust
Biomedical Informatics
Mayo Clinic Rochester
daniel.armbrust(at)mayo.edu
http://informatics.mayo.edu/
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
