On 27 October 2017 at 15:24, Robert Haas <robertmh...@gmail.com> wrote: > On Fri, Oct 27, 2017 at 10:55 AM, Simon Riggs <si...@2ndquadrant.com> wrote: >> Questions? > > I think one of the reasons why Peter Geoghegan decided to pursue > INSERT .. ON CONFLICT UPDATE was that, because it is non-standard SQL > syntax, he felt free to mandate a non-standard SQL requirement, namely > the presence of a unique index on the arbiter columns. If MERGE's > join clause happens to involve equality conditions on precisely the > same set of columns as some unique index on the target table, then I > think you can reuse the INSERT .. ON CONFLICT UPDATE infrastructure > and I suspect there won't be too many problems.
Agreed > However, if it > doesn't, then what? You could decree that such cases will fail, but > that might not meet your use case for developing the feature. Or you > could try to soldier on without the INSERT .. ON CONFLICT UPDATE > machinery, but that means, I think, that sometimes you will get > serialization anomalies - at least, I think, you will sometimes obtain > results that couldn't have been obtained under any serial order of > execution, and maybe it would end up being possible to fail with > serialization errors or unique index violations. > > In the past, there have been objections to implementations of MERGE > which would give rise to such serialization anomalies, but I'm not > sure we should feel bound by those discussions. One thing that's > different is that the common and actually-useful case can now be made > to work in a fairly satisfying way using INSERT .. ON CONFLICT UPDATE; > if less useful cases are vulnerable to some weirdness, maybe it's OK > to just document the problems. Good points. I didn't say it but my intention was to just throw an ERROR if no single unique index can be identified. It could be possible to still run MERGE in that situaton but we would need to take a full table lock at ShareRowExclusive. It's quite likely that such statements would throw duplicate update errors, so I wouldn't be aiming to do anything with that for PG11. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
