On Thu, Nov 02, 2017 at 02:05:19PM -0700, Peter Geoghegan wrote: > Simon Riggs <si...@2ndquadrant.com> wrote: > >So in your view we should make no attempt to avoid concurrent errors, > >even when we have the capability to do so (in some cases) and doing so > >would be perfectly compliant with the SQLStandard. > > Yes. That's what I believe. I believe this because I can't see a way to > do this that isn't a mess, and because ON CONFLICT DO UPDATE exists and > works well for the cases where we can do better in READ COMMITTED mode.
A MERGE mapped to a DML like this: WITH updated AS ( UPDATE <target> SET ... WHERE <condition> RETURNING <target> ) , inserted AS ( INSERT INTO <target> SELECT ... WHERE <key> NOT IN (SELECT <key> FROM updated) AND .. ON CONFLICT DO NOTHING -- see below! RETURNING <target> ) DELETE FROM <target> WHERE <key> NOT IN (SELECT <key> FROM updated) AND <key> NOT IN (SELECT <key> FROM inserted) AND ...; can handle concurrency via ON CONFLICT DO NOTHING in the INSERT CTE. Now, one could write a MERGE that produces conflicts even without concurrency, so adding ON CONFLICT DO NOTHING by default as above... seems not-quite-correct. But presumably one wouldn't write MERGE statements that produce conflicts in the absence of concurrency, so this seems close enough to me. Another thing is that MERGE itself could get an ON CONFLICT clause for the INSERT portion of the MERGE, allowing one to ignore some conflicts and not others, though there would be no need for DO UPDATE, only DO NOTHING for conflict resolution :) This seems better. I do believe this mapping is correct, and could be implemented entirely in src/backend/parser/gram.y! Am I wrong about this? Nico -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers