Re: How to debug a locked backend ?

[snip]
> So what is the UPDATE doing? What is the query (see pg_stat_activity)
> doing? Is it updating a lot of rows? If the query does run for a long
> time holding any kind of lock, you're going to get strange effects like
> this.

The UPDATE is processing ~ 100 rows in a transaction (I'm not sure,
depends on how the application is set up, and it is a parameter in the
query so pg_stat or postgres logs will not show that). The rows are
previously locked by a SELECT ... FOR UPDATE. I can't give you the
actual queries, and I don't have time now to set up a similar case. I
plan to create a test case which does similar processing and let it run
on a test server maybe it can produce the lock.

In any case, I debugged through the process, and there's no exclusive
lock placed by it on any table (checked pg_locks after each executed
step). This is a straightforward processing, without if-branches, so I'm
100% sure it's always executing the same queries. So I'm puzzled by what
could have caused the ExclusiveLock I observed (based on the pg_locks
view during the lockup) on the updated table. You can see it in the
attachment to my post (look for "execute locks(5239)"):

http://archives.postgresql.org/pgsql-general/2005-11/msg00828.php

I have enough information now to know how to get a stack trace, so I'm
prepared for the next time... though I'd prefer there wouldn't be one
;-)

Thanks,
Csaba.