ERROR: index row size 2960 exceeds btree maximum

Started by Rodrigo Sakaiover 18 years ago3 messages
#1Rodrigo Sakai
rodrigo.sakai@poli.usp.br

Hello,

I'm developing an application that needs a different data type. So, I have
implemented this new data type inside postgresql using C, as documentation
shows to.

Basically, the data type is a composition of two timestamps, like:
(timestamp, timestamp) and it is called 'period'. So, almost everything is
fine. Data type is ok, operators are ok, but the index doesn't work fine.

CREATE TABLE employee (

emp_id INT,

emp_name VARCHAR(30),

contract_period PERIOD,

CONSTRAINT pk_employee PRIMARY KEY (emp_id, contract_period)

);

INSERT INTO employee VALUES (1, 'Roger', '(10-jan-2006 , 10-oct-2006)');

INSERT INTO employee VALUES (1, 'Roger', '(10-nov-2006 , 10-dec-2006)');

INSERT INTO employee VALUES (1, 'Roger', '(10-jan-2007 , 10-jul-2008)');

INSERT INTO employee VALUES (1, 'Roger', '(10-aug-2008 , 10-dec-2008)');

SELECT * FROM employee ORDER BY contract_period;

The table creation with contract_period as part of the PK (index) works
ok! The first four insert operations works ok! But, the last insert
operation doesn't work because the first timestamp is from year 2008. Very
strange, isn't?

The select operation using the order by clause doesn't work!

Here is the error about SELECT:

ERROR: cache lookup failed for operator 49158

SQL state: XX000

Here is the error about last INSERT:

ERROR: index row size 2960 exceeds btree maximum, 2713

SQL state: 54000

Hint: Values larger than 1/3 of a buffer page cannot be indexed.

Consider a function index of an MD5 hash of the value, or use full text
indexing.

Any idea?

Thanks!

#2Hannu Krosing
hannu@skype.net
In reply to: Rodrigo Sakai (#1)
Re: ERROR: index row size 2960 exceeds btree maximum

Ühel kenal päeval, K, 2007-05-30 kell 21:41, kirjutas Rodrigo Sakai:

Hello,

I’m developing an application that needs a different data type. So,
I have implemented this new data type inside postgresql using C, as
documentation shows to.

Basically, the data type is a composition of two timestamps, like:
(timestamp, timestamp) and it is called ‘period’. So, almost
everything is fine. Data type is ok, operators are ok, but the index
doesn’t work fine.

...

Here is the error about last INSERT:

ERROR: index row size 2960 exceeds btree maximum, 2713

SQL state: 54000

Hint: Values larger than 1/3 of a buffer page cannot be indexed.

It seems that your C datatype is buggy, generating binary representation
of Period that is bigger than 2713 bytes.

-------------
Hannu

#3Alvaro Herrera
alvherre@commandprompt.com
In reply to: Rodrigo Sakai (#1)
Re: ERROR: index row size 2960 exceeds btree maximum

Rodrigo Sakai wrote:

Basically, the data type is a composition of two timestamps, like:
(timestamp, timestamp) and it is called 'period'. So, almost everything is
fine. Data type is ok, operators are ok, but the index doesn't work fine.

Does type tinterval not suit you? It is not very well documented but it
should work, or at least serve as a basis for developing your own types.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)