Re: Concurrency question

On Tue, Jul 7, 2009 at 3:40 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>> 2009/7/7 Mark Steben <msteben(at)autorevenue(dot)com>:
>>> I ran a vacuum verbose analyze on a database over the weekend.  It ran fine
>>> until it tried to vacuum a table less than 2000 pages.  It successfully
>>> acquired a ShareUpdateExclusiveLock as I would expect.
>>> There was an idle thread that had an AccessSharelock on the same table.
>>> Compatible locks I would think. But the vacuum hung until the
>>> AccessSharelock thread was cancelled - 11 hours in all.
>>> This table normally vacuums in less than 15 seconds.   This AccessSharelock
>>> came from a query that formerly was part of a transaction sent from a remote
>>> server.
>
>> Not sure what you mean by formerly was part of a transaction.  If the
>> transaction has rolled back, then the vacuum can proceed.  If the
>> transaction is till open, then it's not formerly a part of it, it IS a
>> part of it.  Either way, open transactions block vacuum on updated
>> tables.
>
> Uh, no, they don't.
>
> The described situation is impossible: AccessSharelock doesn't block
> ShareUpdateExclusiveLock.  There must have been some other lock or
> attempted lock involved (perhaps at a page or tuple level rather than
> the whole-relation level).  But we can't tell much from this much detail.

So something like alter table or something? I do know that vacuum
full is blocked by updates and such.