Re: Finding the PID keeping a transaction open

Andrew Sullivan <andrew(at)libertyrms(dot)info> writes:
> What I'm trying to figure out is whether there is a way to learn
> which pids are responsible for the long-running transaction(s) that
> touch(es) the candidate tables.

In 7.3 you could look at the pg_locks system view, but I can't think
of any reasonable way to do it in 7.2 :-(

> I think there must be a way with gdb, but I'm stumped.

The lock structures are arcane enough that manual examination with gdb
would take many minutes --- which you'd have to do with the LockMgr lock
held to keep them from changing underneath you. This seems quite
unworkable for a production database ...

It's conceivable that some version of the pg_locks code could be
back-ported to 7.2 --- you'd have to settle for dumping the info to
the log, probably, due to lack of table-function support, but it
could be done.

regards, tom lane