unique index for periods - Mailing list pgsql-general

Hello,

I try to create an unique index for a (time)period, and my goal is to
prevent two overlapping periods in a row.

For this I created a type with following command:

CREATE TYPE period AS
   ("first" timestamp with time zone,
    "next" timestamp with time zone);

To use the btree index I added a compare function:

CREATE OR REPLACE FUNCTION period_compare(period, period)
  RETURNS integer AS $BODY$

begin
   raise info 'compare % <=> % = %', $1, $2,
      CASE
         WHEN $1.next <= $2.first THEN -1
         WHEN $2.next <= $1.first THEN 1
         ELSE 0
      END;

   return
      CASE
         WHEN $1.next <= $2.first THEN -1
         WHEN $2.next <= $1.first THEN 1
         ELSE 0
      END;
end

$BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 1;

After this I created a operator class:

CREATE OPERATOR CLASS period_overlap
   DEFAULT FOR TYPE period USING btree AS
   FUNCTION 1 period_compare(period, period);

To test everything I use this table:

CREATE TABLE p (
  p period NOT NULL,
  CONSTRAINT p_pkey PRIMARY KEY (p)
);

Now I fill the table with data:

DELETE FROM p;
-- clean up
VACUUM p;

INSERT INTO p VALUES (('-infinity', 'today')::period);
-- this one fails
-- INSERT INTO p VALUES (('-infinity', 'infinity')::period);

DELETE FROM p;
-- the index tree is still there, why?

INSERT INTO p VALUES (('-infinity', 'infinity')::period);
-- intersects with the deleted value, so compare returns 0
-- and the data goes to the left side of the tree

-- this one should fail
INSERT INTO p VALUES (('today', 'infinity')::period);
-- but this one is bigger than the deleted value, goes to
-- the right side of the tree and is not compared to the
-- entry inserted above.

What do I do wrong? Is there another solution to solve my problem?

Thanks,
  Gerhard