unique index for periods

From: Gerhard Heift <ml-postgresql-20081012-3518(at)gheift(dot)de>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: unique index for periods
Date: 2009-08-20 06:58:19
Message-ID: 20090820065819.GA2598@gheift.kawo1.rwth-aachen.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sim Zacks 2009-08-20 07:36:37 Re: multiple paramters in aggregate function
Previous Message Craig Ringer 2009-08-20 02:33:10 Re: Problem with bacula and 8.3/8.4