Re: Question about RI checks - Mailing list pgsql-hackers

On Thu, Oct 23, 2014 at 2:41 PM, Florian Pflug <fgp@phlo.org> wrote:
> The only reason we need the crosscheck snapshot to do that is because
> children may have been added (and the change committed) *after* the
> transaction which removed the parent has taken its snapshot, but *before*
> that transaction locks the parent row.
>
> My proposal is to instead extend the locking protocol to prevent that.
> Essentially, we have to raise a serialization error whenever
>
>   1) We attempt to exclusively lock a row (this includes updating or deleting
>      it), and
>
>   2) somebody else did hold a share lock on that row recently, and
>
>   3) That somebody is invisible to us according to our snapshot.
>
> My initial attempt to do that failed, because we used to have very little
> means of storing the locking history - the only source of information was
> the xmax field, so any update of a tuple removed information about previous
> lock holders - even if that update was later aborted. I pondered using
> multi-xids for this, but at the time I deemed that too risky - back at the
> time, they had a few wraparound issues and the like which were OK for share
> locks, but not for what I intended.
>
> But now that we have KEY SHARE locks, the situation changes. We now rely on
> multi-xids to a much greater extent, and AFAIK multi-xid wraparound is now
> correctly dealt with. We also already ensure that the information contained
> in multi-xids are preserved across tuple upgrades (otherwise, updating a row
> on which someone holds a KEY SHARE lock would be broken).
>
> So all that is missing, I think, is
>
>   1) To make sure we only remove a multi-xid if none of the xids are invisible
>      to any snapshot (i.e. lie before GlobalXmin or something like that).
>
>   2) When we acquire a lock (either explicitly or implicitly by doing an
>      UPDATE or DELETE) check if all previous committed lock holders are
>      visible according to our snapshot, and raise a serialization error
>      if not.

I don't think you can count on being able to figure out all of the
recent lockers by looking at xmax; it can get overwritten.  For
example, suppose transaction A locks the row and then commits.  Then
transaction B comes along and again locks the row and commits.  My
understanding is that B won't create a multi-xact since there's just
one locker; it'll just stomp on the previous xmax.

Now, you could change that, but I suspect it would have pretty drastic
implications on systems that have both foreign keys and long-running
transactions.

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