Re: [HACKERS] MERGE SQL Statement for PG11

On Mon, Mar 26, 2018 at 12:16 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 26 March 2018 at 16:09, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Mon, Mar 26, 2018 at 5:53 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> Since we now have MVCC catalog scans, all the name lookups are
>>> performed using the same snapshot so in the above scenario the newly
>>> created object would be invisible to the second name lookup.
>>
>> That's not true, because each lookup would be performed using a new
>> snapshot -- not all under one snapshot.
>
> You're saying we take a separate snapshot for each table we lookup?
> Sounds weird to me.

I'm saying we take a separate snapshot for each and every catalog
lookup, except when we know that no catalog changes can have occurred.
See the commit message for 568d4138c646cd7cd8a837ac244ef2caf27c6bb8.
If you do a lookup in pg_class and 3 lookups in pg_attribute each of
the 4 can be done under a different snapshot, in the worst case.
You're not the first person to believe that the MVCC catalog scan
patch fixes that problem, but as the guy who wrote it, it definitely
doesn't. What that patch fixed was, prior to that patch, a catalog
scan might find the WRONG NUMBER OF ROWS, like you might do a lookup
against a unique index for an object that existed and, if the row was
concurrently updated, you might find 0 rows or 2 rows instead of 1
row. IOW, it guaranteed that we used a consistent snapshot for each
individual lookup, not a consistent snapshot for the whole course of a
command.

> So this error could happen in SELECT, UPDATE, DELETE or INSERT as well.
>
> Or you see this as something related specifically to MERGE, if so how?
> Please explain what you see.

As I said before, the problem occurs if the same command looks up the
same table name in more than one place. There is absolutely nothing
to guarantee that we get the same answer every time. As far as I
know, the proposed MERGE patch has that issue an existing DML commands
don't; but someone else may have better information.

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