Re: Deferrable Unique Constraints

Neil Conway <neilc(at)samurai(dot)com> writes:
> You could perhaps relax the uniqueness of the index during the
> transaction itself, and keep around some backend-local indication of
> which index entries it have been inserted. Then at transaction-commit
> you'd need to re-check the inserted index entries to verify that they
> are unique.

Yeah, what I've been visualizing is a list of "tentative duplicates" ---
that is, you do the immediate unique check same as now, and if it passes
(which hopefully is most of the time) then you're in the clear.
Otherwise you log the apparent duplicate key value to be rechecked at
commit.

> It would be nice to just keep a pin on the leaf page that we
> inserted into, although we'd need to take care to follow subsequent page
> splits (could we use the existing L & Y techniques to do this?).

I do not believe we can do that without risking deadlocks. It'll be
safer just to repeat the search for each key value that's of concern.

regards, tom lane