Re: SR standby hangs

On Tue, Feb 22, 2011 at 11:34 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> 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.
>
>> 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
>
> We have seen deadlocks arising from this type of scenario:
>
>        autovac has vacuum lock on table X
>        autovac blocks waiting for cleanup lock on buffer B in X
>        process P has pin on B due to a suspended query (eg cursor)
>        P tries to get exclusive lock on X, is blocked by autovac's lock
>
> The heavyweight-lock manager fails to recognize deadlock because it
> doesn't know about the buffer-level LWLock.
>
>> It might be interesting to have autovacuum skip a block it finds
>> pinned for too long.
>
> +1, although as somebody pointed out nearby, this will only be legal if
> it's not a vacuum-to-prevent-wraparound situation.

Another approach to this problem would be to jigger things so that the
query doesn't hold a buffer pin while suspended. I'm not quite sure
how to make that work, but maybe it's possible.

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