Re: index row size exceeds btree maximum, 2713 - Solutions? - Mailing list pgsql-general

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



      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=codingschemename&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=codingschemename&">Drop




      conceptcode
      character varying(100)

      NOT NULL



      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=conceptcode&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=conceptcode&">Drop




      propertyid
      character varying(50)

      NOT NULL



      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=propertyid&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=propertyid&">Drop




      property
      character varying(250)

      NOT NULL



      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=property&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=property&">Drop




      language
      character varying(32)




      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=language&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=language&">Drop




      presentationformat
      character varying(50)




      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=presentationformat&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=presentationformat&">Drop




      datatype
      character varying(50)




      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=datatype&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=datatype&">Drop




      ispreferred
      boolean




      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=ispreferred&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=ispreferred&">Drop




      degreeoffidelity
      character varying(50)




      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=degreeoffidelity&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=degreeoffidelity&">Drop




      matchifnocontext
      boolean




      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=matchifnocontext&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=matchifnocontext&">Drop




      representationalform
      character varying(50)




      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=representationalform&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=representationalform&">Drop




      propertyvalue
      text

      NOT NULL



      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=propertyvalue&">Alter
      <a

href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=propertyvalue&">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.