Re: Overlapping time ranges constraints in 8.4

I thought that this was going to be tricky.

Perhaps I could use rules to populate a shadow table that is like a INNER JOIN of the two tables. This would consolidate the Boolean on the separate table into the same table that holds the time ranges. In version 8.4 I would still need to use explicit locking on this shadow table but in 9.x an exclusion constraint may have a better chance of working.

Thanks for your help on this one.

> In my triggers (PL/pgSQL) I am using a expression like this
>
> SELECT B.* INTO v_overlapping from INNER JOIN campus ON
> (campus.id=B.campus_id)
> where campus.colA = 't' AND (campus.start_time,
> campus.stop_time) OVERLAPS (NEW.start_time, NEW.stop_TIME);
>
> I am worried that the transaction serialization will not do the
> predicate locking that is needed for concurrent inserts/updates.

To get that sort of predicate locking in PostgreSQL, you must be using version 9.1 or later and the transactions must be using the serializable transaction isolation level. But for something like this, you might be better off using the "exclusion constraint"
feature of 9.0 and later. (The only reason I say "might" instead of "would" is that I'm not sure that feature can handle the complication of the boolean in a separate table.)

> Can I use add a FOR UPDATE clause to my SELECT INTO expression in
> PL/pgSQL ?

That won't help -- it just locks the actual rows read; it doesn't protect against insertion of conflicting rows. You could use explicit locking to actually serialize the transactions which do this. There are other options, but none of them are pretty.

-Kevin

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general