Re: Vacuum as "easily obtained" locks

Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> writes:
> On Wed, Aug 3, 2011 at 10:57 AM, Michael Graham <mgraham(at)bloxx(dot)com> wrote:
>> On Wed, 2011-08-03 at 10:17 -0400, Tom Lane wrote:
> Michael Graham <mgraham(at)bloxx(dot)com> writes:
>>>> Would my applications
>>>> constant polling of the queue mean that the lock could not be easily
>>>> obtained?

>>> Very possible, depending on what duty cycle is involved there.

>> Hmm. The clients aren't that aggressive, especially when they failed to
>> find data on a previous select, there are 4 clients, they each poll
>> every 10 seconds and the select runs in <1ms.
>>
>> It might be worth noting that they don't ever disconnect from the
>> server, but I assume that is not an issue for getting the
>> AccessExclusiveLock on the table?

> I don't think so, unless they keep the transaction open.

The other problem is that once autovacuum has gotten the lock, it has to
keep it for long enough to re-scan the truncatable pages (to make sure
they're still empty). And it is set up so that any access to the table
will kick autovacuum off the lock. An access pattern like that would
very likely prevent it from ever truncating, if there are a lot of pages
that need to be truncated. (There's been some discussion of modifying
this behavior, but nothing's been done about it yet.)

regards, tom lane