Nico Williams <n...@cryptonector.com> wrote:
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 ...;
This is a bad idea. An implementation like this is not at all maintainable.
can handle concurrency via ON CONFLICT DO NOTHING in the INSERT CTE.
That's not handling concurrency -- it's silently ignoring an error. Who is to say that the conflict that IGNORE ignored is associated with a row visible to the MVCC snapshot of the statement? IOW, why should the DELETE affect any row? There are probably a great many reasons why you need a ModifyTable executor node that keeps around state, and explicitly indicates that a MERGE is a MERGE. For example, we'll probably want statement level triggers to execute in a fixed order, regardless of the MERGE, RLS will probably require explicitly knowledge of MERGE semantics, and so on. FWIW, your example doesn't actually have a source (just a target), so it isn't actually like MERGE. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
