Re: [HACKERS] MERGE SQL Statement for PG11
От | Peter Geoghegan |
---|---|
Тема | Re: [HACKERS] MERGE SQL Statement for PG11 |
Дата | |
Msg-id | 20171102222548.GA3581@marmot обсуждение исходный текст |
Ответ на | Re: [HACKERS] MERGE SQL Statement for PG11 (Nico Williams <nico@cryptonector.com>) |
Ответы |
Re: [HACKERS] MERGE SQL Statement for PG11
Re: [HACKERS] MERGE SQL Statement for PG11 |
Список | pgsql-hackers |
Nico Williams <nico@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
В списке pgsql-hackers по дате отправления: