Bryan Field-Elliot <bryan_lists@netmeme.org> writes:
> We have a running server (v7.3) which, over time, seems to accumulate a
> bunch of "stuck" PostgreSQL instances which appear to be dead inside of
> a transaction. For example, "ps" reveals many of these:
> postgres 17683 0.0 0.4 7376 1032 ? S Nov13 0:21 postgres:
> bryan sourceid 127.0.0.1 idle in transaction
"idle in transaction" means the backend is waiting for its connected
client to issue another command. I don't think it's Postgres that you
need to be investigating ...
> I can't see a query for "given a process ID currently
> in a transaction, show me all the queries which have been run in it so
> far".
There is no such query, but you could try turning on query logging and
then studying the postmaster log.
regards, tom lane