Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e8CiK-0000xo-BY for pgsql-hackers@arkaria.postgresql.org; Fri, 27 Oct 2017 22:02:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e8CiJ-0003Xh-Bv for pgsql-hackers@arkaria.postgresql.org; Fri, 27 Oct 2017 22:02:51 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e8Cgk-0000lX-0J for pgsql-hackers@postgresql.org; Fri, 27 Oct 2017 22:01:14 +0000 Received: from p3plsmtpa08-05.prod.phx3.secureserver.net ([173.201.193.106]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e8Cgc-0001it-RG for pgsql-hackers@postgresql.org; Fri, 27 Oct 2017 22:01:12 +0000 Received: from [54.210.254.150] ([54.210.254.150]) by :SMTPAUTH: with SMTP id 8CgZeTSCB5REt8CgbeAkGn; Fri, 27 Oct 2017 15:01:05 -0700 Content-Type: multipart/alternative; boundary="----sinikael-?=_1-15091416652340.8814466651529074" From: Serge Rielau To: Peter Geoghegan Cc: PostgreSQL Hackers In-Reply-To: References: <1509138807748-0.post@n3.nabble.com> Subject: Re: MERGE SQL Statement for PG11 Message-Id: <766a601b-655c-403d-a1cd-9922cddcdcad@rielau.com> Date: Fri, 27 Oct 2017 15:00:47 -0700 X-Cm-Message-Id: 15091416474380616b472f04c65b6e58e9db1ff9e9659cf059f3ac8f6afdc1192105212 X-Cm-Draft-Id: WyJhIiwzLCJkcmFmdF9pZCIsIjE1MDkxNDE2NDcwMzgiLCJ2IiwxXQ== X-Cm-Tracking-Code: 2.0/1509141647/647b813270dffd4c4b2631f3c9eb3f46/3/9cf3801d03770ada01bb39dc8f52321d/17af54bb092f7010e401e28f7adae0f1/619f5dd6cf0c728abb479c46d4dbfbe4 X-Mailer: Newton MIME-Version: 1.0 X-CMAE-Envelope: MS4wfItPko1UGRBhLBon1J7dD60pcw0O8Bqq5dhjbSLLRe4lomSPdijsbBxZ7OEbtXEhgDtWyT+zbvURNU2U8hIxm+ET9VkI55GTJqfPtN81UilB5j+lMiLj hzmexlGK4cC8SUewsF7I+mIE0Y8QtZS7F9/348k8dtU2PDjpzyT4iuuIKm13u5p+o3jw0m4t5FL7gA== List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org ------sinikael-?=_1-15091416652340.8814466651529074 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=3Ddx&cv=3D9.8.= 79&pv=3D10.12.6&source=3Demail_footer_2] On Fri, Oct 27, 2017 at 2:42 PM, = Peter Geoghegan wrote: On Fri, Oct 27, 2017 at 2:13 PM, = srielau 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=E2=80=9D 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=E2=80=99m trying to make is that I haven=E2=80=99t seen the need for the = extra work I had to do once the feature appeared in the wild. Cheers Serge ------sinikael-?=_1-15091416652340.8814466651529074 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable



=
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=E2=80=9D 
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=E2=80=99m trying to make is that I haven=E2=80=99t seen the need = for the extra work I had to do once the feature appeared in the wild.=

C= heers
Serge
------sinikael-?=_1-15091416652340.8814466651529074--