Re: [HACKERS] MERGE SQL Statement for PG11

On Tue, Jan 30, 2018 at 8:27 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> As far as I am able to understand, the substantive issue here is what
> to do when we match an invisible tuple rather than a visible tuple.
> The patch currently throws a serialization error on the basis that you
> (Simon) thought that's what was previously agreed. Peter is arguing
> that we don't normally issue a serialization error at READ COMMITTED
> (which I think is true) and proposed that we instead try to INSERT. I
> don't necessarily think that's different from consensus to implement
> option #3 from https://www.postgresql.org/message-id/CA%2BTgmoYOyX4nyu9mbMdYTLzT9X-1RptxaTKSQfbSdpVGXgeAJQ%40mail.gmail.com
> because that point #3 says that we're not going to try to AVOID errors
> under concurrency, not that we're going to create NEW errors.

I should have mentioned earlier that you have this exactly right: I do
not want to make any special effort to avoid duplicate violation
errors. I also don't want to create any novel new kind of error (e.g.,
READ COMMITTED serialization errors).

That having been said, I think that Simon was correct to propose a
novel solution. It just seemed like READ COMMITTED serialization
errors were the wrong novel solution, because that takes the easy way
out. ISTM that the right thing is to adapt EvalPlanQual() (or READ
COMMITTED conflict handling more generally) to the new complication
that is multiple "WHEN ... AND" quals (that need to be evaluated one
at a time, a bona fide new requirement). In short, his novel solution
seemed much too novel.

As I've pointed out already, we will define MERGE to users as
something that "provides a single SQL statement that can conditionally
INSERT, UPDATE or DELETE rows, a task that would otherwise require
multiple procedural language statements". I believe that MERGE's
charter should be to live up to that definition in the least
surprising way possible, up to and including preserving the
maybe-surprising aspects of how multiple procedural language
statements can behave when the system does READ COMMITTED conflict
handling. That's my opinion in a nutshell.

--
Peter Geoghegan