Re: locked backend

On Wed, 2005-11-16 at 19:41, Tom Lane wrote:
> 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.
>
> Updates do not block inserts, and neither does vacuum, so there's
> something you're not telling us. In particular an UPDATE wouldn't
> take an ExclusiveLock on the table, so that lock must have come from
> some other operation in the same transaction.

Well, if I'm not telling you something is because I don't know it myself
:-)

OK, that makes sense with something else done before blocking the
inserts and not the update. In any case the transaction of the update
was locking the rest, as nothing else was running at the moment I
checked.

BTW, is the "ExclusiveLock" a table lock ? From the documentation of
"pg_locks" it is not completely clear (it refers to
http://www.postgresql.org/docs/8.0/static/explicit-locking.html#LOCKING-TABLES which does not enumerates these names used in pg_lock).

I wonder what would take an exclusive lock on the table ?

I would exclude any alter table, we don't do that from our application,
and the other person who could have done an alter table beside me sits
next to me and he didn't do it (the update's SQL comes from the
application actually). There are no foreign keys on the table, just a
primary key on a field populated from a sequence (by our application,
not via a default clause). We do not lock the table explicitly. The only
locking is done via a SELECT...FOR UPDATE, could that cause a table lock
?

But whatever the cause of the lock would be, I still wonder why was the
UPDATE hanging ? This table is a temporary table, it is regularly
filled-emptied, and usually it is relatively small (max a few tens of
thousands of rows), so an update running more than 3 hours is not
kosher. The update is part of the emptying procedure actually.

If it was some kind of deadlock, why was it not detected ? And why the
backend didn't respond to the kill signal ?

I'm shooting around in the dark, but I have to find out what happened,
so I can avoid it next time... or at least be able to shut down
efficiently a backend which blocks my server's activity...

Thanks,
Csaba.