Re: Need help writing exclusion constraint

Jeff Davis writes:
> On Sat, 2011-01-15 at 21:32 +0100, Tomas Vondra wrote:
> > > ALTER TABLE event ADD CONSTRAINT event_overlap
> > > CHECK(overlap_at_dest(destination_id, starts, ends));
> >
> > There's a race condition
>
> ...
>
> > One way to fix this is locking
>
> I do not recommend locking. In fact, the primary reason that exclusion
> constraints exist is to prevent unnecessary locking for problems exactly
> like this.
>
> I included some links in my other reply that demonstrate how to avoid
> that excessive locking while still being safe from race conditions.

I totally understand the issues of race conditions. My original reply
didn't address the issue...should have. Of course race conditions are
only an issue for concurrent sessions...that depends on the total
application architecture.

Anyway...Jeff, all your answers depend on using new features in 9.0.
What would you recommend for folk still using 8.4? Without 9.0
exclusion constraints, what else can you do besides using functions in
check constraints (or triggers) with appropriate locking (at some
level of the overall application architecture).

Dan