Horton

Re: support for MERGE

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Álvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Amit Langote <amitlangote09@gmail.com>, Japin Li <japinli@hotmail.com>, Zhihong Yu <zyu@yugabyte.com>, Simon Riggs <simon.riggs@enterprisedb.com>, pgsql-hackers@lists.postgresql.org, Tomas Vondra <tomas.vondra@enterprisedb.com>, Daniel Westermann <dwe@dbi-services.com>, Erik Rijkers <er@xs4all.nl>, Jaime Casanova <jcasanov@systemguards.com.ec>, Andres Freund <andres@anarazel.de>
Date: 2022-08-09T09:48:23Z
Lists: pgsql-hackers 
On 2022-Aug-01, Álvaro Herrera wrote:

> > >      If <command>MERGE</command> attempts an <command>INSERT</command>
> > >      and a unique index is present and a duplicate row is concurrently
> > > +    inserted, then a uniqueness violation error is raised;
> > > +    <command>MERGE</command> does not attempt to avoid such
> > > +    errors by evaluating <literal>MATCHED</literal> conditions.
> > 
> > This was a portion of a chang that was committed as ffffeebf2.
> > 
> > But I don't understand why this changed from "does not attempt to avoid the
> > error by executing an <command>UPDATE</command>." to "...by evaluating
> > <literal>MATCHED</literal> conditions."
> > 
> > Maybe it means to say "..by re-starting evaluation of match conditions".
> 
> Yeah, my thought there is that it may also be possible that the action
> that would run if the conditions are re-run is a DELETE or a WHEN
> MATCHED THEN DO NOTHING; so saying "by executing an UPDATE" it leaves
> out those possibilities.  IOW if we're evaluating NOT MATCHED INSERT and
> we find a duplicate, we do not go back to MATCHED.

So I propose to leave it as

           If <command>MERGE</command> attempts an <command>INSERT</command>
           and a unique index is present and a duplicate row is concurrently
           inserted, then a uniqueness violation error is raised;
           <command>MERGE</command> does not attempt to avoid such
           errors by restarting evaluation of <literal>MATCHED</literal>
	   conditions.

(Is "re-starting" better than "restarting"?)

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos (bis) / con todos los humanos acabaré ¡acabaré! (Bender)

Commits

  1. Doc fixes for MERGE statement

  2. MERGE docs adjustments

  3. Link to MVCC docs in MERGE docs

  4. Fix EXPLAIN MERGE output when no tuples are processed

  5. Make EXPLAIN MERGE output format more compact

  6. Add support for MERGE SQL command

  7. Split ExecUpdate and ExecDelete into reusable pieces

  8. Add API of sorts for transition table handling in trigger.c

  9. Represent Lists as expansible arrays, not chains of cons-cells.