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

[Dan Armbrust]

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 <[EMAIL PROTECTED]> 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.