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

[Dan Armbrust <daniel.armbrust.list@xxxxxxxxx>]

We have built a Model for terminologies that we call The Lexical Grid (more info http://informatics.mayo.edu/LexGrid/index.php)

LexGrid has multiple backend data storage mechanisms, including LDAP and SQL.  We do our best to remain implementation independent - our SQL implementations, for example can run against MS Access, DB2, MySQL and PostgreSQL.

I'm currently trying to load a new terminology into a PosgreSQL backend, and arrived at this error because it happens to have a couple of very large data values that get mapped into the 'propertyvalue' field.

The structure of the table that I am (currently) having problems with is: (apologies if your client does not parse HTML - this is what is convenient to me right now)

codingschemename	character varying(70)	NOT NULL		Alter	Drop
conceptcode	character varying(100)	NOT NULL		Alter	Drop
propertyid	character varying(50)	NOT NULL		Alter	Drop
property	character varying(250)	NOT NULL		Alter	Drop
language	character varying(32)			Alter	Drop
presentationformat	character varying(50)			Alter	Drop
datatype	character varying(50)			Alter	Drop
ispreferred	boolean			Alter	Drop
degreeoffidelity	character varying(50)			Alter	Drop
matchifnocontext	boolean			Alter	Drop
representationalform	character varying(50)			Alter	Drop
propertyvalue	text	NOT NULL		Alter	Drop

The structure of the table is not easily changed, as it closely follows our model.

The index that is failing is CREATE INDEX i1 ON conceptproperty USING btree (codingschemename, property, propertyvalue).

Usually, the 'propertyValue' field is fairly short - 100 chars or less.  And in those cases, I need to be able to do an indexed search on it.  In this particular case, this terminology has a propertyValue that is very long.  I can't just toss it.  I don't really care if it makes it into the index - for a terminology with as much data as this has, I'll be using Lucene to do text searches anyway - but I do need to be able to return the full propertyValue in response to a propertyId query.

I also don't want to negatively affect the performance of the rest of the DB just to accommodate this instance - hence the questions about the implications of changing the BLCKSZ variable.

Dan



Bruno Wolff III wrote:

On Mon, Jul 18, 2005 at 14:44:26 -0500,
  Dan Armbrust <daniel.armbrust.list@xxxxxxxxx> 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.
    

Can you explain how you are using the index now and what you are trying
to accomplish? It is hard to suggest alternatives without knowing what
you are really trying to do.