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