Thread: Can't create index on text column

Can't create index on text column

From
"Eric Jain"
Date:
========================================================================
====
                        POSTGRESQL BUG REPORT TEMPLATE
========================================================================
====


Your name  : Eric Jain
Your email address : Eric.Jain@isb-sib.ch


System Configuration
---------------------
  Architecture (example: Intel Pentium)   : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : Linux 2.4.18-19.8.0smp

  PostgreSQL version (example: PostgreSQL-7.3):   PostgreSQL-7.3.3

  Compiler used (example:  gcc 2.95.2)  : gcc 3.2


Please enter a FULL description of your problem:
------------------------------------------------

Can't create index on text column containing a specific value:

ERROR:  index_formtuple: data takes 8720 bytes, max is 8191


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

1)

CREATE TABLE statements (
  model TEXT NOT NULL,
  statement TEXT,
  subject TEXT NOT NULL,
  predicate TEXT NOT NULL,
  object_resource TEXT,
  object_string TEXT,
  object_number DOUBLE PRECISION
)
  WITHOUT OIDS
;

2) COPY statements FROM 'statements.tab'; /* See attachment */

3) CREATE INDEX object_string_index ON statements(object_string);

Note: Can't reproduce this error with other values, even if they are
larger. But I do not see what is special about the offending string, it
doesn't seem to contain any characters outside the range A-Z.

Interestingly, octet_length(object_string) does not produce 8720, but
8797.


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

A temporary workaround may be to create an index on
"substr(object_string for 100)"...