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

On Mon, Jan 13, 2014 at 1:53 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> 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.

For what it's worth, I agree with Heikki. There's probably nothing
sensible an upsert can do if it conflicts with more than one tuple,
but if it conflicts with just exactly one, it oughta be OK.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company