ERROR: index row size 2960 exceeds btree maximum
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!
Ü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
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)