Skip site navigation (1) Skip section navigation (2)

Re: SR standby hangs

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, Andrew Dunstan <amdunstan(at)nc(dot)rr(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: SR standby hangs
Date: 2011-02-28 22:00:59
Message-ID: AANLkTikxf9GULn6V5hdD7xYs-9fA1QgrEDjanb4WsYkr@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Josh BerkusDate: 2011-02-28 22:05:08
Subject: Re: Re: Why our counters need to be time-based WAS: WIP: cross column correlation ...
Previous:From: Heikki LinnakangasDate: 2011-02-28 22:00:54
Subject: Re: [HACKERS] Re: PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group