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



  Any idea?



Reply via email to