Re: Unique constraints for non-btree indexes

On Wed, Jan 18, 2006 at 04:10:16PM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > check_unique_index( ctid of inserting tuple, ctid of possibly
> > conflicting tuple)
>
> I agree it's pretty ugly to have the index AM directly poking into
> the heap, but adding a level of subroutine doesn't really make that
> a whole lot nicer :-(.

Well, the raionale is that in theory the same logic would be applied
for GiST indexes, so it'd be nice to do it in one place rather than
repeat it for each index AM.

> In any case, you've underestimated the amount of coupling here: if
> the conflicting tuple is dead, _bt_check_unique also wants to know
> just how dead it is, so it can possibly set LP_DELETE on the old index
> entry.

Hmm, ok. There's more info that the index AM would like, but the same
info would be required for both GiST and b-tree, no? (assuming GiST has
the same delete optimisation)

> My own thoughts about deferred unique checks have been along the lines
> of storing the possibly-conflicting key value when the initial check
> notes a problem, and then repeating the index search at commit.

Well, I didn't want to exclude that possibility. Racing with VACUUM is
a tricky one. If we keep an array of ctid in memory, we need to know if
VACUUM removes one of them. OTOH, the recheck will then return either
a blank or a tuple which definitly doesn't match.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.