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@gmail.com> 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.