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

On 01/11/2014 12:40 AM, Peter Geoghegan wrote:
> My problem is that in general I'm not sold on the actual utility of
> making this kind of row locking work with exclusion constraints. I'm
> sincerely having a hard time thinking of a practical use-case
> (although, as I've said, I want to make it work with IGNORE). Even if
> you work all this row locking stuff out, and the spill-to-disk aspect
> out, the interface is still wrong, because you need to figure out a
> way to project more than one reject per slot. Maybe I lack imagination
> around how to make that work, but there are a lot of "ifs" and "buts"
> either way.

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.

- Heikki