On 26 March 2018 at 17:52, Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Mar 26, 2018 at 12:16 PM, Simon Riggs <si...@2ndquadrant.com> wrote: >> On 26 March 2018 at 16:09, Robert Haas <robertmh...@gmail.com> wrote: >>> On Mon, Mar 26, 2018 at 5:53 AM, Simon Riggs <si...@2ndquadrant.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.
That all makes sense, thanks for explaining. I spent a few more minutes, going "but", "but" though I can now see good reasons for everything to work this way. >> 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. I will look deeper and report back. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
