Also note that an uncommitted select statement will lock the table and
prevent vacuum from running. It isn't just inserts/updates that will
lock and cause vacuum to block, but selects as well. This got me in the
past. (Of course this is all fixed in 7.2 with the new vacuum
functionality that doesn't require exclusive locks on the tables).
thanks,
--Barry
Michael Meskes wrote:
> On Thu, Oct 11, 2001 at 08:26:48PM -0400, Tom Lane wrote:
>
>>You evidently have some client applications holding open transactions
>>
>
> Okay, I know where to look for that. Thanks.
>
>
>>that have locks on some tables. That's not a deadlock --- at least,
>>
>
> It is no deadlock if the transaction holding the lock remains idle and does
> nothing. But I cannot imagine how this could happen.
>
> What happens if there is a real deadlock, i.e. the transaction holding the
> lock tries to lock a table vacuum already locked? Ah, I just checked and
> rendered my last mail useless. It appears the backend does correctly detect
> the deadlock and kill one transaction.
>
>
>>it's not Postgres' fault. The VACUUM is waiting to get exclusive access
>>to some table that's held by one of these clients, and the COPY is
>>probably queued up behind the VACUUM.
>>
>
> So the reason is that the transaction does hold a lock but does not advance
> any further?
>
> Michael
>