Re: SSI predicate locking on heap -- tuple or row? - Mailing list pgsql-hackers

Robert Haas  wrote:
> I think the implementation is what matters here. I understand that
> there are certain situations in which the user might choose to
> UPDATE a row and other situations in which they might choose to
> DELETE and then INSERT: but the user's intent is basically
> irrelevant.
We don't consider it irrelevant when we decide which triggers to
fire.  We do have update triggers distinct from the insert and delete
triggers.  We also consider it relevant when dealing with a write
conflict in READ COMMITTED mode.  Those facts make me very reluctant
to move based on a simple assertion that it doesn't matter.
> If the system treats those operations in basically the same way,
> then it shouldn't be necessary to follow the CTID chain in one case
> given that there is no CTID chain in the other case. Or to put that
> another way, if it is necessary to follow the CTID chain, then we
> should be able to articulate a reason for that necessity --
> something that is materially different in the UPDATE case.
There is a wealth of research on which SSI is based.  I've read many
(although by no means *all*) of the papers on the topic, and all of
the ones I've read have been based around the concept of a row which
can be updated and retain its identity.  I trust the research, but I
think it is incumbent on us to prove, rather than just assert, that
it can be applied just as well to a row-version tuple.  I sure hope
it can, because we can have faster, leaner, less fragile code that
way.  I've attempted to write out a proof; although I won't trust
that without further review -- by me and by others.
> Otherwise, we're just following the chain "because it's there".
Why would you say it *is* there?
> It seems to me that we can actually state with some degree of
> precision what that "material difference" would need to be. The
> goal of SSI is to prevent serialization anomalies that would not be
> prevented by snapshot isolation. Let's suppose that it successfully
> does that in the DELETE/INSERT case. Suppose further that we change
> SSI so that it handles the UPDATE case in the same way that it
> handles the DELETE/INSERT case. This change will be incorrect only
> if there is a serialization anomaly that snapshot isolation *would
> have prevented* in the DELETE/INSERT case that *it does not
> prevent* in the update case.
I don't see that -- it could be correct because of the conceptual
difference between an UPDATE and a DELETE/INSERT pair.
> In other words, if SSI needs to be more rigorous in the UPDATE
> case, it can only be because snapshot isolation is less rigorous in
> that case, and the additional rigor that SSI must apply there must
> be exactly equal to whatever snapshot isolation isn't picking up
> (as compared with the DELETE/INSERT case).
>
> Does that make any sense? It seems logical to me, but IJWH.
I've always loved logic, but one of the most intriguing aspects is
identifying the unproven assumptions in an argument.  You have a
built-in premise that there is no significant difference between an
UPDATE and a DELETE/INSERT pair, in which case the logic is flawless
which is leading you to the conclusion that a lock on the visible
tuple is enough.  I'm not confident in that premise, so the simple
argument doesn't persuade me.
> Your argument seems reasonable to me;
Thanks much for fighting through it.  It is heartening that you
couldn't punch any holes in it.
> but it would be nice if we could find a simpler one, because
> simpler arguments are less likely to be incorrect. :-)
All generalizations are false.  :-)
-Kevin