Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Mon, Jan 13, 2014 at 12:23 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> Exclusion constraints can be used to implement uniqueness checks with
> SP-GiST or GiST indexes. For example, if you want to enforce that there are
> no two tuples with the same x and y coordinates, ie. use a point as the key.
> You could add a b-tree index just to enforce the constraint, but it's better
> if you don't have to. In general, it's just always better if features don't
> have implementation-specific limitations like this.

That seems rather narrow. Among other things, I worry about the
baggage for users in documenting supporting SP-GiST/GiST. "We support
it, but it only really works for the case where you're using exclusion
constraints as unique constraints, something that might make sense in
certain narrow contexts, contrary to our earlier general statement
that a unique index should be preferred there". We catalog amcanunique
methods as the way that we support unique indexes. I really do feel
that that's the appropriate level to support the feature at, and I
have not precluded other amcanunique implementations from doing the
same, having documented the intended value locking interface/contract
for the benefit of any future amcanunique AM author. It's ON DUPLICATE
KEY, not ON OVERLAPPING KEY, or any other syntax suggestive of
exclusion constraints and their arbitrary commutative operators.

--
Peter Geoghegan