By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,488 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Stuck processes, viewing the queries inside

P: n/a
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

As admin, I'd like to run "psql" and peer inside the system (using the
Statistics Collector) to see which queries have been run inside some of
these stuck transactions. It will help me debug what's going wrong.

This page:

http://www.postgresql.org/docs/7.3/s...ing-stats.html

gives lots of intereseting methods for pulling information on the
running state, but 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".

Any pointers would be appreciated,

Bryan

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Bryan Field-Elliot <br*********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

P: n/a
Bryan Field-Elliot <br*********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.