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