Re: Stuck processes, viewing the queries inside

Bryan Field-Elliot <bryan_lists(at)netmeme(dot)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