Re: Stuck processes, viewing the queries inside - Mailing list pgsql-general

From Tom Lane
Subject Re: Stuck processes, viewing the queries inside
Date
Msg-id 1457.1069306502@sss.pgh.pa.us
Whole thread Raw
In response to Stuck processes, viewing the queries inside  (Bryan Field-Elliot <bryan_lists@netmeme.org>)
List pgsql-general
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

pgsql-general by date:

Previous
From: "K. Deepa"
Date:
Subject: Is it possible to find a size of a table
Next
From: Shridhar Daithankar
Date:
Subject: Re: Humor me: Postgresql vs. MySql (esp. licensing)