Re: Vacuum as "easily obtained" locks

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.

> My worry at the moment is that because the table is so large the vacuum
> takes a very long time to run (one has been running for 5hrs) and I
> assume it will continue to run until it is able to get the
> AccessExclusiveLock is so desperately wants.
>

You can run a CLUSTER command on the table which would guarantee that
an exclusive lock is taken and the table is compacted, but remember
that it will block out your select queries until the command
completes.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com