Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Thu, Sep 25, 2014 at 9:20 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I've never been a fan of putting the index name in there.

Me neither. Although I do understand Kevin's concern about the user's
intent surrounding which unique index to merge on.

> I agree
> that's stuff that a DML statement shouldn't need to know about. What
> I've advocated for in the past is specifying the list of columns that
> should be used to determine whether to insert or update. If you have
> a match on those columns, update the row; else insert. Any other
> unique indexes stand or fall as may be.
>
> I still think that idea has merit.

As I've said, my problem with that idea is the corner cases. Consider
the possible ambiguity. Could DML queries in production start failing
("ambiguous unique index specification") because the DBA created a new
unique index on attributes that somewhat overlap the attributes of the
unique index that the DML author actually meant? What about the
effects of BEFORE triggers, and their interaction with partial unique
indexes? If you can describe an exact behavior that overcomes these
issues, then I'll give serious consideration to implementing it. As
things stand, to be perfectly honest it sounds like a footgun to me.
There are interactions that make getting it right very ticklish.

I don't want to make a unique index specification mandatory because
that's ugly - that's the only reason, TBH. However, while what you
describe here accomplishes the same thing without being ugly, it is
potentially very surprising. Naming the unique index directly has the
great advantage of very clearly demonstrating user intent.

--
Peter Geoghegan