Re: ALTER TABLE lock strength reduction patch is unsafe

On Fri, Dec 16, 2011 at 8:54 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Fri, Dec 16, 2011 at 1:38 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Fri, Dec 16, 2011 at 7:07 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> That is a bug and one we should fix. I supplied a patch for that,
>>> written to Tom's idea for how to solve it.
>>>
>>> I will apply that, unless there are objections.
>>
>> I remember several attempts at that, but I don't remember any that
>> didn't meet with objections.  Do you have a link?
>
> My patch to implement SnapshotNow correctly, from Jun 27 on this
> thread was never reviewed or commented upon by anybody. That was
> probably because it only fixes one of the problems, not all of them.

Well, I think it was mostly because you didn't sound terribly
optimistic about the approach: "So maybe this idea is worth doing, but
I don't think it helps us much reduce lock levels for DDL." And also
because you described the patch as a "rough hack", and not something
you thought ready to commit.

I am also not entirely sure I believe that this is plugging all the
failure cases. I think that it may just be making the failure cases
more obscure, rather than really getting rid of them. Consider
something like the following:

T1: Update row version A, creating new row version B.
T2: Begin scanning the catalog in question. We happen to encounter
row version B first. We remember T1's XID as in progress, but don't
see the row since T1 hasn't committed.
T1: Rollback.
T3: Update row version A, creating new row version C.
T3: Commit.
T2: Scan now comes to row version A; we don't see that version either,
since T3 is committed.

I don't think there's any guarantee that T2's scan will see tuples
inserted after the start of the scan. If I'm correct about that, and
I'm pretty sure it's true for sequential scans anyway, then T2's scan
might end without seeing C either.

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