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

Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> Heikki, Andres and I are against using MERGE for this, fwiw. Tom
> seemed to think so too, on previous occasions. It isn't a matter
> of alternative syntaxes. I have described in detail why I think
> it's a bad idea - I have linked to that about 3 times in this
> thread.

Yeah, I read that, and I'm not convinced.

> It paints us into a corner when we go to make this do what MERGE
> is supposed to do. Do you want a feature that, when fully
> generalized, plays a special visibility game based on whether or
> not some exact set of conditions are met? That is a non-starter,
> IMV.

For other queries we use different access techniques not only
based on the presence of an index, but on the state of the
visibility map, degree of bloat, ordering of tuples in a heap,
etc. -- so sure, I'm OK with different execution styles based on
whether your join conditions match a unique index on columns that
can't be NULL.

> The whole idea of using an arbitrary join syntax seems great,
> but I need something that works backwards from would-be unique
> violations. That's the only way to preserve the UPSERT guarantees
> (atomicity, definite insert or update).

I absolutely don't buy that it is the *only way*. It is probably
(by far) the *easiest* way, and doing so gets us a frequently-
requested feature; but I think limiting the initial implementation
to cases where the join conditions include equality tests on all
columns of some appropriate unique index is fine, and doesn't seem
to me to preclude further development of the MERGE feature for
additional cases. In fact, I think having something to build on is
a plus.

The claims that you can't get a duplicate key error with an UPSERT
are completely bogus, IMV. The *best* you can do is avoid them on
the index used for matching (unless you're willing to ignore
problem input rows or mangle the data in surprising ways to avoid
such an error on a second unique index). With a fully functional
MERGE syntax you could eventually gain the ability to write
exceptions like that to the location of your choice (be it a table
or WARNING messages in the log).

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