| From: | Dan Armbrust <daniel(dot)armbrust(dot)list(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: index row size exceeds btree maximum, 2713 - Solutions? |
| Date: | 2005-07-18 20:17:35 |
| Message-ID: | 42DC0E5F.1080208@gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
We have built a Model for terminologies that we call The Lexical Grid
(more info <a class="moz-txt-link-freetext" href="http://informatics.mayo.edu/LexGrid/index.php">http://informatics.mayo.edu/LexGrid/index.php</a>)<br>
<br>
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.<br>
<br>
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.<br>
<br>
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)<br>
<br>
<table>
<tbody>
<tr>
<td class="data1">codingschemename</td>
<td class="data1">character varying(70)</td>
<td class="data1">
<div align="center">NOT NULL</div>
</td>
<td class="data1"><br>
</td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=codingschemename&">Alter</a></td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=codingschemename&">Drop</a></td>
<td class="data1"><br>
</td>
</tr>
<tr>
<td class="data2">conceptcode</td>
<td class="data2">character varying(100)</td>
<td class="data2">
<div align="center">NOT NULL</div>
</td>
<td class="data2"><br>
</td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=conceptcode&">Alter</a></td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=conceptcode&">Drop</a></td>
<td class="data2"><br>
</td>
</tr>
<tr>
<td class="data1">propertyid</td>
<td class="data1">character varying(50)</td>
<td class="data1">
<div align="center">NOT NULL</div>
</td>
<td class="data1"><br>
</td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=propertyid&">Alter</a></td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=propertyid&">Drop</a></td>
<td class="data1"><br>
</td>
</tr>
<tr>
<td class="data2">property</td>
<td class="data2">character varying(250)</td>
<td class="data2">
<div align="center">NOT NULL</div>
</td>
<td class="data2"><br>
</td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=property&">Alter</a></td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=property&">Drop</a></td>
<td class="data2"><br>
</td>
</tr>
<tr>
<td class="data1">language</td>
<td class="data1">character varying(32)</td>
<td class="data1"><br>
</td>
<td class="data1"><br>
</td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=language&">Alter</a></td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=language&">Drop</a></td>
<td class="data1"><br>
</td>
</tr>
<tr>
<td class="data2">presentationformat</td>
<td class="data2">character varying(50)</td>
<td class="data2"><br>
</td>
<td class="data2"><br>
</td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=presentationformat&">Alter</a></td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=presentationformat&">Drop</a></td>
<td class="data2"><br>
</td>
</tr>
<tr>
<td class="data1">datatype</td>
<td class="data1">character varying(50)</td>
<td class="data1"><br>
</td>
<td class="data1"><br>
</td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=datatype&">Alter</a></td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=datatype&">Drop</a></td>
<td class="data1"><br>
</td>
</tr>
<tr>
<td class="data2">ispreferred</td>
<td class="data2">boolean</td>
<td class="data2"><br>
</td>
<td class="data2"><br>
</td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=ispreferred&">Alter</a></td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=ispreferred&">Drop</a></td>
<td class="data2"><br>
</td>
</tr>
<tr>
<td class="data1">degreeoffidelity</td>
<td class="data1">character varying(50)</td>
<td class="data1"><br>
</td>
<td class="data1"><br>
</td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=degreeoffidelity&">Alter</a></td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=degreeoffidelity&">Drop</a></td>
<td class="data1"><br>
</td>
</tr>
<tr>
<td class="data2">matchifnocontext</td>
<td class="data2">boolean</td>
<td class="data2"><br>
</td>
<td class="data2"><br>
</td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=matchifnocontext&">Alter</a></td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=matchifnocontext&">Drop</a></td>
<td class="data2"><br>
</td>
</tr>
<tr>
<td class="data1">representationalform</td>
<td class="data1">character varying(50)</td>
<td class="data1"><br>
</td>
<td class="data1"><br>
</td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=representationalform&">Alter</a></td>
<td class="opbutton1"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=representationalform&">Drop</a></td>
<td class="data1"><br>
</td>
</tr>
<tr>
<td class="data2">propertyvalue</td>
<td class="data2">text</td>
<td class="data2">
<div align="center">NOT NULL</div>
</td>
<td class="data2"><br>
</td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=properties&database=LexGrid2&schema=public&table=conceptproperty&column=propertyvalue&">Alter</a></td>
<td class="opbutton2"><a
href="http://mir04.mayo.edu/pgadmin/tblproperties.php?action=confirm_drop&database=LexGrid2&schema=public&table=conceptproperty&column=propertyvalue&">Drop</a></td>
<td class="data2"><br>
</td>
</tr>
</tbody>
</table>
<br>
The structure of the table is not easily changed, as it closely follows
our model.<br>
<br>
The index that is failing is CREATE INDEX i1 ON conceptproperty USING
btree (codingschemename, property, propertyvalue).<br>
<br>
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.<br>
<br>
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.<br>
<br>
Dan<br>
<br>
<br>
<br>
Bruno Wolff III wrote:
<blockquote cite="mid20050718195433(dot)GA23508(at)wolff(dot)to" type="cite">
<pre wrap="">On Mon, Jul 18, 2005 at 14:44:26 -0500,
Dan Armbrust <a class="moz-txt-link-rfc2396E" href="mailto:daniel(dot)armbrust(dot)list(at)gmail(dot)com"><daniel(dot)armbrust(dot)list(at)gmail(dot)com></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap=""><!---->
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.
</pre>
</blockquote>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 11.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jaime Casanova | 2005-07-18 20:27:51 | Re: index row size exceeds btree maximum, 2713 - Solutions? |
| Previous Message | Bruno Wolff III | 2005-07-18 19:54:33 | Re: index row size exceeds btree maximum, 2713 - Solutions? |