Re: Deferrable Unique Constraints

George Essig <george_essig(at)yahoo(dot)com> writes:

> I noticed that implementing deferrable unique constraints is on the
> TODO list. I don't think its been said yet, but currently you can
> implement a deferrable unique constraint by using a deferrable
> constraint trigger together with a procedural language like plpgsql.

You have a race condition. Two transactions can insert conflicting records and
if they commit at the same time they would both not see each other's
uncommitted records.

Off the top of my head it seems the way to go about doing this would be to
simply not insert the records in the index until commit time. This doesn't
actually sound so hard, is there any problem with this approach?

You could almost implement this with a deferred trigger, a boolean column, and
a partial unique index. However I don't think deferred constraint triggers can
modify the record data.

The unfortunate bit here is that even if this worked the trigger setting the
boolean flag which puts the record into the index would create a new copy of
the record. Since it's modifying a record inserted by the same transaction it
could in theory just modify it in place. I don't think any attempt is made to
do that though. In any case a real native implementation wouldn't really need
the flag so this problem wouldn't come up.

--
greg