Re: SR standby hangs

On Tue, Feb 22, 2011 at 12:55 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> A little OT, but ISTM that the buffer pin mechanism by its nature is
> prone to lock upgrade hazards.  A cleanup lock is essentially an
> access exclusive lock on the buffer, while a buffer pin is an access
> share lock.  In the middle, we have the usual share and exclusive
> (content) locks.  We regularly try to upgrade buffer pins to any of
> the higher locking levels, which is quite unlike what we do with
> regular (heavyweight) locks, where we take some pains to avoid lock
> upgrades.

Except that pins don't block exclusive locks so there's no deadlock risk.

The oddity here is on Vacuums super-exclusive "lock" which is the real
equivalent of an "exclusive lock". However there's the added bonus
that there can only be one vacuum on a table at a time. That makes it
safe

It might be interesting to have autovacuum skip a block it finds
pinned for too long. Since it's willing to abort entirely if someone
gets a conflicting lock skipping a block doesn't seem like a big deal.
It won't let us advance the frozenxid but it'll speed up a subsequent
vacuum.

At some point I wonder if we'll find ourselves wanting to keep track
of frozenxid per block and keep a kind of priority queue of blocks to
vacuum based on their age. I continue to wonder whether we're
reinventing standard garbage collection processes.

Incidentally, even if we allowed multiple vacuum processes per table I
think it could be coded to be safe as long as each vacuum only needs
to acquire the super exclusive lock on a single block at a time and
doesn't try to acquire other locks while holding it.

--
greg