Re: MERGE SQL Statement for PG11

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: Nico Williams <nico(at)cryptonector(dot)com>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: MERGE SQL Statement for PG11
Date: 2017-11-02 22:25:48
Message-ID: 20171102222548.GA3581@marmot
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Nico Williams <nico(at)cryptonector(dot)com> wrote:
>A MERGE mapped to a DML like this:
> 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

>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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andreas Karlsson 2017-11-02 22:33:18 Re: GnuTLS support
Previous Message Nico Williams 2017-11-02 22:00:35 Re: MERGE SQL Statement for PG11