Re: locked backend

Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:

> The situation (diagnosed via pg_stat_activity): one table was locked by
> an update, a VACUUM ANALYZE was running for the same table (triggered by
> autovacuum), and a handful of inserts were waiting on the same table.
> After some checking around (see below for details), I decided to kill
> the UPDATE (it was holding up the inserts which means lost data for us
> and danger of connection starvation), and did it with "kill processid"
> from the command line, but no success: the backend didn't respond. Then
> I killed the VACUUM which exited promptly, the UPDATE was still blocked.
> Then I remembered there is a pg_cancel_backend function (seen it
> recently on this list), and tried that too, without success - the update
> was still blocked, and blocking all inserts.

Are there any foreign key relationships involved here? Because barring foreign
key constraints none of these should have blocked any of the others.

The only thing that would block a plain VACUUM (as opposed to VACUUM FULL)
would be if you had a transaction pending that had a schema change like ALTER
TABLE. Or something that had done an explicit LOCK TABLE.

And the only thing that would block the INSERTs are the above or if there's a
foreign key relationship to another table and that record in the other is
locked, from another INSERT that refers to it or from an UPDATE.

The only way your explanation that the UPDATE was holding up the inserts makes
sense is if the records you were UPDATEing were referred to by the records you
were inserting in a foreign key reference.

Even so it shouldn't have held up the VACUUM at all.

--
greg