Re: index row size exceeds btree maximum, 2713 - Solutions?

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/