Re: WIP: Deferrable unique constraints

Jeff Davis wrote:
> First, I'm happy that you're working on this; I think it's important. I
> am working on another index constraints feature that may have some
> interaction:
>
> http://archives.postgresql.org/pgsql-hackers/2009-07/msg00302.php
>
> Let me know if you see any potential conflicts between our work.
>

Hi Jeff,

Yes, I've been following that other thread with interest. I don't think
that there is any conflict between the 2 patches, although there may be
some interaction.

My stuff is specific to UNIQUE, and my triggers make the assumption that
this is enforced by an index. The triggers don't actually care what kind
of index, although of course currently it can only be a btree.

I guess that your generalised constraints could be made deferrable in a
similar way, and would use different triggers to do the final check, so
no problem there.

> On Tue, 2009-07-07 at 19:38 +0100, Dean Rasheed wrote:
>> For potential uniqueness violations a
>> deferred trigger is queued to do a full check at the end of the
>> statement or transaction, or when SET CONSTRAINTS is called. The
>> trigger then replays the insert in a "fake insert" mode, which doesn't
>> actually insert anything - it just checks that what is already there
>> is unique, waiting for other transactions if necessary.
>
> What does the deferred trigger do? Do you need a "fake insert" mode or
> can you use an index search instead?
>

Well the point about the "fake insert" mode is that it has to be able to
block on another transaction, to see if it commits or rolls back a
potentially conflicting value.

ISTM that trying to do this with an index search would open up the
potential for all sorts of race conditions.

- Dean

> I'm thinking that you could just store the TID of the tuple that causes
> the potential violation in your list. Then, when you recheck the list,
> for each potential violation, find the tuple from the TID, do a search
> using the appropriate attributes, and if you get multiple results
> there's a conflict.
>
> Regards,
> Jeff Davis
>
>