Re: Need help writing exclusion constraint

Dne 15.1.2011 21:07, Daniel Popowich napsal(a):
> CREATE OR REPLACE FUNCTION overlap_at_dest(dest integer,
> s timestamp,
> e timestamp)
> returns boolean as $_$
>
> DECLARE
> c bigint;
> BEGIN
> select count(*) into c from event
> where (destination_id = dest)
> and ((starts, ends) overlaps (s,e));
> return c = 0;
> END;
>
> $_$ LANGUAGE plpgsql;
>
>
>
> Then alter your table:
>
> ALTER TABLE event ADD CONSTRAINT event_overlap
> CHECK(overlap_at_dest(destination_id, starts, ends));

There's a race condition - if there are two concurrent sessions, both
inserting rows for the same destination_id, this trigger won't work I
guess as the session does not see the rows inserted by the other one
(this is due to the READ COMMITED isolation level).

One way to fix this is locking - in this case you have to make sure that
two sessions modifying the same destination_id will synchronize
properly. The easiest way to od that is to lock the same row in some
table - e.g. if you have a "destinations" table lock the row with the
same destination_id. So the function should look something like this

CREATE OR REPLACE FUNCTION overlap_at_dest(dest integer,
s timestamp,
e timestamp)
returns boolean as $_$
DECLARE
c bigint;
BEGIN

PERFORM * FROM destinations WHERE destination_id = dest FOR UPDATE;

select count(*) into c from event
where (destination_id = dest)
and ((starts, ends) overlaps (s,e));
return c = 0;
END;

$_$ LANGUAGE plpgsql;

Or something like that. If there's no suitable table, you can use
advisory locks - just replace the PERFORM with

pg_advisory_lock(dest);

regards
Tomas