Re: Re: VACUUM is hanging

Ashley Clark <aclark(at)ghoti(dot)org> writes:
>> Try not to leave open transactions hanging around in your Apache
>> code. An idle database connection couldn't block VACUUM like that;
>> it'd have to have been in the middle of a BEGIN block, or maybe even
>> an unfinished query.

> That appears to be the culprit. I don't really understand why an
> uncommitted SELECT statement could/should lock the table though. How
> does that differ from just an open transaction where no query has
> happened yet?

If you've just done BEGIN then you aren't holding any locks. But having
done a SELECT, you have a read lock on that table, which will persist
until you commit or abort the transaction. A read lock doesn't normally
prevent other transactions from doing things with the table --- but it
does block "exclusive" locks, which are obtained for actions like DROP
TABLE, ALTER TABLE, and VACUUM. Those guys want to know that there
ain't no one messin' with that table.

regards, tom lane