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!
