Re: [HACKERS] MERGE SQL Statement for PG11

On Tue, Jan 30, 2018 at 4:45 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> "You're introducing a behavior/error"... No, I advocate nothing in the
> patch, I am merely following the agreement made here:
>
> https://www.postgresql.org/message-id/CA%2BTgmoYOyX4nyu9mbMdYTLzT9X-1RptxaTKSQfbSdpVGXgeAJQ%40mail.gmail.com
>
> Robert, Stephen, may we attempt to implement option 4 as Peter now suggests?

Simon, I don't think you should represent Peter as having changed his
position when he has already said that he didn't. To be honest, the
discussion up to this point suggests to me that Peter has a
significantly better grip of the issues than you do, yet your posts
convey, at least to me, that you're quite sure he's wrong about a lot
of stuff, including whether or not his current statements are
compatible with his previous statements. I think that the appropriate
course of action is for you and he to spend a few more emails trying
to actually get on the same page here.

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. In
other words, I understand Peter, then and now, to be saying that MERGE
should behave just as if invisible tuples didn't exist at all; if that
leads some other part of the system to throw an ERROR, then that's
what happens. Presumably, in a case like this, that would be a common
outcome, because the merge would be performed on the basis of a unique
key and so inserting would trigger a duplicate key violation. But
maybe not, because I don't think MERGE requires there to be a unique
key on that column, so maybe the insert would just work, or maybe the
conflicting transaction would abort just in time to let it work
anyway.

It is possible that I am confused, here, among other reasons because I
haven't read the code, but if I'm not confused then Peter's analysis
is correct.

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