Re: MERGE SQL Statement for PG11

From: Nico Williams <nico(at)cryptonector(dot)com>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
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:59:25
Message-ID: 20171102225924.GW4496@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote:
> Nico Williams <nico(at)cryptonector(dot)com> wrote:
> >A MERGE mapped to a DML like this:
> This is a bad idea. An implementation like this is not at all
> maintainable.

Assuming the DELETE issue can be addressed, why would this not be

> >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?

Ah, yes, we'd have to make sure the DELETE does not delete rows that
could not be inserted. There's... no way to find out what those would
have been -- RETURNING won't mention them, though it'd be a nice
addition to UPSERT to have a way to do that, and it'd make this mapping

> 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.

Wouldn't those fire anyways in a statement like the one I mentioned?

> FWIW, your example doesn't actually have a source (just a target), so it
> isn't actually like MERGE.

That can be added. I was trying to keep it pithy.


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Fabrízio de Royes Mello 2017-11-02 23:36:45 Re: [PATCH] A hook for session start
Previous Message Tom Lane 2017-11-02 22:34:06 Re: pgbench - use enum for meta commands