Re: [HACKERS] MERGE SQL Statement for PG11 - Mailing list pgsql-hackers

From Robert Haas
Subject Re: [HACKERS] MERGE SQL Statement for PG11
Date
Msg-id CA+TgmoZuce2iHSBVpkX5qud-UbxhtY85opA47LTP=QW16J47wg@mail.gmail.com
Whole thread Raw
In response to [HACKERS] MERGE SQL Statement for PG11  (Simon Riggs <simon@2ndquadrant.com>)
Responses Re: [HACKERS] MERGE SQL Statement for PG11
Re: [HACKERS] MERGE SQL Statement for PG11
List pgsql-hackers
On Fri, Oct 27, 2017 at 10:55 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Questions?

I think one of the reasons why Peter Geoghegan decided to pursue
INSERT .. ON CONFLICT UPDATE was that, because it is non-standard SQL
syntax, he felt free to mandate a non-standard SQL requirement, namely
the presence of a unique index on the arbiter columns.  If MERGE's
join clause happens to involve equality conditions on precisely the
same set of columns as some unique index on the target table, then I
think you can reuse the INSERT .. ON CONFLICT UPDATE infrastructure
and I suspect there won't be too many problems.  However, if it
doesn't, then what?  You could decree that such cases will fail, but
that might not meet your use case for developing the feature.  Or you
could try to soldier on without the INSERT .. ON CONFLICT UPDATE
machinery, but that means, I think, that sometimes you will get
serialization anomalies - at least, I think, you will sometimes obtain
results that couldn't have been obtained under any serial order of
execution, and maybe it would end up being possible to fail with
serialization errors or unique index violations.

In the past, there have been objections to implementations of MERGE
which would give rise to such serialization anomalies, but I'm not
sure we should feel bound by those discussions.  One thing that's
different is that the common and actually-useful case can now be made
to work in a fairly satisfying way using INSERT .. ON CONFLICT UPDATE;
if less useful cases are vulnerable to some weirdness, maybe it's OK
to just document the problems.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: [HACKERS] Re: Burst in WAL size when UUID is used as PK whilefull_page_writes are enabled
Next
From: Tom Lane
Date:
Subject: Re: [HACKERS] [GENERAL] Postgres 10 manual breaks links with anchors