Re: freezing multixacts

On Mon, Feb 6, 2012 at 9:31 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
>> Suppose you have a tuple A which is locked by a series of transactions
>> T0, T1, T2, ...; AIUI, each new locker is going to have to create a
>> new mxid with all the existing entries plus a new one for itself.
>> But, unless I'm confused, as it's doing so, it can discard any entries
>> for locks taken by transactions which are no longer running.
>
> That's correct.  But the problem is a tuple that is locked or updated by
> a very old transaction that doesn't commit or rollback, and the tuple is
> never locked again.  Eventually the Xid could remain live while the mxid
> is in wraparound danger.

Ah, I see.  I think we should probably handle that the same way we do
for XIDs: try to force autovac when things get tight, then start
issuing warnings, and finally just refuse to assign any more MXIDs.

Another thing that might make sense, for both XIDs and MXIDs, is to
start killing transactions that are preventing vacuum/autovacuum from
doing their thing.  This could mean either killing the people who are
holding back RecentGlobalXmin, so that we can actually freeze the old
stuff; or killing people who are holding a conflicting lock, using the
recovery-conflict stuff or some adaptation of it.  We've made it
fairly difficult to avoid having autovacuum run at all with the
xidVacLimit/xidStopLimit stuff, but there's still no real defense
against autovacuum running but failing to mitigate the problem, either
because there's a long-running transaction holding a snapshot open, or
because someone is sitting on a relation or buffer lock.  This of
course is off-topic from your patch here...

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