Steve Crawford <scrawford@pinpointresearch.com> writes:
> A couple hundred processes were showing as "startup waiting" and one
> was "idle in transaction". The process in the "VACUUM waiting" state
> was the only one connected to that database - all other connections
> were to other databases.
I suspect what must have happened is that the vacuum process was trying
to vacuum one of the shared catalogs (pg_database or pg_shadow), and was
blocked trying to get exclusive lock because someone else (the "idle in
transaction" guy) was holding some lock on that table. At this point
all incoming connections, to any database, will block behind the VACUUM
until the idle guy closes his transaction and thereby releases his lock.
> I tracked down the process that was "idle in transaction" and it was a
> pg_dump process running on another machine. This process does a
> periodic dump of one very small table and should complete in a
> fraction of a second but was still waiting since the previous day -
> apparently without deleterious effects.
What was it waiting on? Since it was idle instead of waiting, the
problem must have been on the client side. I've not heard of pg_dump
just going to sleep for no reason...
> I've stopped running the vacuum full job via cron till I can trust it.
> Any ideas on how to track/prevent this behavior? Server is version
> 7.4.1 and my web searches have proved futile.
My recommendation would be to lose the --full. If you're doing
sufficiently frequent vacuuming you have no need for that, and getting
rid of it means vacuum doesn't take exclusive table locks. That means
it will neither block nor be blocked by ordinary readers and writers.
regards, tom lane