Re: Promise index tuples for UPSERT

Peter Geoghegan <pg(at)heroku(dot)com> wrote:
On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> wrote:

>> Instead of naming the index, you should name the columns, and
>> the system can look up the index or indexes that match those
>> columns.

+1

That is what I have been consistently requesting instead of index
names, every time I notice it mentioned.

> It's not totally clear that we need *any* WITHIN clause, BTW.
> I'm not dead set on it. It was something I mainly added at
> Kevin's request. I do see the risks, though.

What I said was in response to your assertion that your technique
would *never* generate a duplicate key error. As others have again
been pointing out, when there is more than one unique index you can
have rows to apply which cannot be applied accurately without
causing such an error. What I requested was that the behavior in
those cases be clear and documented. I didn't take a position on
whether you pick an index or ignore the input row (with a
warning?), but we need to decide how it is handled. I have made
the same point Heikki is making, though -- we have no business
referencing an index name in the statement.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company