On Thu, Nov 02, 2017 at 12:51:45PM -0700, Peter Geoghegan wrote: > Nico Williams <n...@cryptonector.com> wrote: > >If you want to ignore conflicts arising from concurrency you could > >always add an ON CONFLICT DO NOTHING to the INSERT DML in the mapping I > >proposed earlier. Thus a MERGE CONCURRENTLY could just do that. > > > >Is there any reason not to map MERGE as I proposed? > > Performance, for one. MERGE generally has a join that can be optimized > like an UPDATE FROM join.
Ah, right, I think my mapping was pessimal. How about this mapping instead then: 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 .. /* * Add ON CONFLICT DO NOTHING here to avoid conflicts in the face * of concurrency. */ RETURNING <target> ) DELETE FROM <target> WHERE <key> NOT IN (SELECT <key> FROM updated) AND <key> NOT IN (SELECT <key> FROM inserted) AND ...; ? If a MERGE has no delete clause, then the mapping would be: WITH updated AS ( UPDATE <target> SET ... WHERE <condition> RETURNING <target> ) INSERT INTO <target> SELECT ... WHERE <key> NOT IN (SELECT <key> FROM updated) AND .. /* * Add ON CONFLICT DO NOTHING here to avoid conflicts in the face * of concurrency. */ ; > I haven't studied this question in any detail, but FWIW I think that > using CTEs for merging is morally equivalent to a traditional MERGE > implementation. [...] I agree. So why not do that initially? Optimize later. Such a MERGE mapping could be implemented entirely within src/backend/parser/gram.y ... Talk about cheap to implement, review, and maintain! Also, this would be notionally very simple. Any optimizations to CTE query/DML execution would be generic and applicable to MERGE and other things besides. If mapping MERGE to CTE-using DMLs motivates such optimizations, all the better. > [...]. It may actually be possible to map from CTEs to a MERGE > statement, but I don't think that that's a good approach to implementing > MERGE. Surely not every DML with CTEs can map to MERGE. Maybe I misunderstood your comment? > Most of the implementation time will probably be spent doing things like > making sure MERGE behaves appropriately with triggers, RLS, updatable > views, and so on. That will take quite a while, but isn't particularly > technically challenging IMV. Note that mapping to a DML with CTEs as above gets triggers, RLS, and updateable views right from the get-go, because DMLs with CTEs, and DMLs as CTEs, surely do as well. Nico -- -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers