From: Serge Rielau <serge@rielau.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: MERGE SQL Statement for PG11
Date: Fri, 27 Oct 2017 15:00:47 -0700
Message-ID: <766a601b-655c-403d-a1cd-9922cddcdcad@rielau.com> (raw)
In-Reply-To: <CAH2-WzkZjBp9ha-Fb+deZEPp6JmW3R-kMg9xeVnG5sLFwOCBBQ@mail.gmail.com>
References: <CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com>
<CA+TgmoZuce2iHSBVpkX5qud-UbxhtY85opA47LTP=QW16J47wg@mail.gmail.com>
<CANP8+j+jG76Okkibk7Xu=T6C5-gSpXX9sudANS9nGyR9yWEgRA@mail.gmail.com>
<CA+TgmobMx66NWCoje=PEGnbMQQdZojJi7v9RqsuY23vkNsTf1Q@mail.gmail.com>
<1509138807748-0.post@n3.nabble.com>
<CAH2-WzkZjBp9ha-Fb+deZEPp6JmW3R-kMg9xeVnG5sLFwOCBBQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx&cv=9.8.79&pv=10.12.6&source=email_footer_2]
On Fri, Oct 27, 2017 at 2:42 PM, Peter Geoghegan <pg@bowt.ie> wrote:
On Fri, Oct 27, 2017 at 2:13 PM, srielau <serge@rielau.com> wrote:
> While the standard may not require a unique index for the ON clause I have
> never seen a MERGE statement that did not have this property. So IMHO this
> is a reasonable restrictions.
The Oracle docs on MERGE say nothing about unique indexes or
constraints. They don't even mention them in passing. They do say
"This statement is a convenient way to combine multiple operations. It
lets you avoid multiple INSERT, UPDATE, and DELETE DML statements."
SQL Server's MERGE docs do mention unique indexes, but only in
passing, saying something about unique violations, and that unique
violations *cannot* be suppressed in MERGE, even though that's
possible with other DML statements (with something called
IGNORE_DUP_KEY).
What other systems *do* have this restriction? I've never seen one that did. Not clear what you are leading up to here. When I did MERGE in DB2 there was also no limitation: " Each row in the target can only be operated on once. A row in the target can only be identified as MATCHED with one row in the result table of the table-reference” What there was however was a significant amount of code I had to write and test to enforce the above second sentence. IIRC it involved, in the absence of a proof that the join could not expand, adding a row_number() over() AS rn over the target leg of the join and then a row_number() over(partition by rn) > 1 THEN RAISE_ERROR() to catch violators. Maybe in PG there is a trivial way to detect an expanding join and block it at runtime.
So the whole point I’m trying to make is that I haven’t seen the need for the extra work I had to do once the feature appeared in the wild.
Cheers Serge
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: serge@rielau.com, pg@bowt.ie
Subject: Re: MERGE SQL Statement for PG11
In-Reply-To: <766a601b-655c-403d-a1cd-9922cddcdcad@rielau.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox