pgsql-bugs@postgresql.org writes:
> what fixed it was killing off a bunch of remote psql and DBI clients.
> nothing was killed on the postmaster host. the result was that all
> hung psql/DBI processes completed normally, all hung children of the
> postmaster seemed to complete normally, and the "vacuum analyze"
> started actually chewing up CPU and I/O, completing normally about
> five minutes later (which is the usual total run time.)
My guess is that you had a client that was holding a lock on some
table that's used by most of your clients. All this would take is
not closing an open transaction after reading/writing the table.
Then vacuum comes along and wants an exclusive lock on that table,
so it sits and waits. Then everyone else comes along and wants to
read or write that same table. Normally, their requests would not
conflict with the read or write lock held by the original client
... but they do conflict with vacuum's exclusive-lock request, so
they stack up behind the vacuum.
As far as Postgres is concerned, there's no deadlock here, only a
slow client. But it's a fairly annoying scenario anyway, since a
client that's hung on some external condition can block everyone else
indirectly through the background VACUUM.
7.2 will use non-exclusive locks for vacuuming (by default, if I
get my way about it), which should make this sort of problem much
less frequent.
> i am upgrading to 7.1.2 on the postmaster,
Good idea --- 7.1b3 had a number of nasty bugs. But I doubt this
is one of them.
regards, tom lane