Re: foreign key locks, 2nd attempt - Mailing list pgsql-hackers

On Tue, Jan 24, 2012 at 03:47:16PM -0300, Alvaro Herrera wrote:
> The biggest item remaining is the point you raised about multixactid
> wraparound.  This is closely related to multixact truncation and the way
> checkpoints are to be handled.  If we think that MultiXactId wraparound
> is possible, and we need to involve autovacuum to keep it at bay, then I

To prove it possible, we need prove there exists some sequence of operations
consuming N xids and M > N multixactids.  Have N transactions key-lock N-1
rows apiece, then have each of them key-lock one of the rows locked by each
other transaction.  This consumes N xids and N(N-1) multixactids.  I believe
you could construct a workload with N! multixact usage, too.

Existence proofs are one thing, real workloads another.  My unsubstantiated
guess is that multixactid use will not overtake xid use in bulk on a workload
not specifically tailored to do so.  So, I think it's enough to notice it,
refuse to assign a new multixactid, and tell the user to clear the condition
with a VACUUM FREEZE of all databases.  Other opinions would indeed be useful.

> think the only way to make that work is to add another column to
> pg_class so that each table's oldest multixact is tracked, same as we do
> with relfrozenxid for Xids.  If we do that, I think we can do away with
> most of the MultiXactTruncate junk I added -- things would become a lot
> simpler.  The cost would be bloating pg_class a bit more.  Are we okay
> with paying that cost?  I asked this question some months ago and I
> decided that I would not add the column, but I am starting to lean the
> other way.  I would like some opinions on this.

That's not the only way; autovacuum could just accelerate normal freezing to
advance the multixactid horizon indirectly.  Your proposal could make it far
more efficient, though.

> You asked two questions about WAL-logging locks: one was about the level
> of detail we log for each lock we grab; the other was about
> heap_xlog_update logging the right info.  AFAICS, the main thing that
> makes detailed WAL logging necessary is hot standbys.  That is, the
> standby must have all the locking info so that concurrent transactions
> are similarly locked as in the master ... or am I wrong in that?  (ISTM
> this means I need to fix heap_xlog_update so that it faithfully
> registers the lock info we're storing, not just the current Xid).

Standby servers do not need accurate tuple locks, because it's not possible to
wait on a tuple lock during recovery.  (By the way, the question about log
detail was just from my own curiosity.  We don't especially need an answer to
move forward with this patch, unless you want one.)

> * Columns that are part of the key
>   Noah thinks the set of columns should only consider those actually referenced
>   by keys, not those that *could* be referenced.

Well, do you disagree?  To me it's low-hanging fruit, because it isolates the
UPDATE-time overhead of this patch to FK-referenced tables rather than all
tables having a PK or PK-like index (often just "all tables").

>   Also, in a table without columns, are all columns part of the key, or is the
>   key the empty set?  I changed HeapSatisfiesHOTUpdate but that seems arbitrary.

It does seem arbitrary.  What led you to switch in a later version?

Thanks,
nm