472,981 Members | 1,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,981 software developers and data experts.

Stuck processes, viewing the queries inside

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
2 2926
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Bruce Sandell | last post by:
I have a c# application where I run a query every 10 seconds. After the query completes, I close the connection object, close the oledbdatareader object and dispose of the command object. ...
4
by: pierre.bru | last post by:
hi, fist the context: I have a web server which query a mySql database. but as the number of parralel queries increase, the server slows down too much. I got 2 ideas, one of which is to run N...
9
by: Abhishek Srivastava | last post by:
Hello All, In IIS 6.0 We have a concept of worker processes and application pools. As I understand it, we can have multiple worker process per appliction pool. Each worker process is dedicated...
5
by: Jeremy | last post by:
I have a core VB service that monitors a database, and based on data in the records will execute code to send email notifications. Problem: I don't want my main program code to halt and wait for...
2
by: Egyd Csaba | last post by:
Hi All, I'm wonder if there is any possibility to kick out automatically stuck in queries after say 10 minutes or so? I mean some kind of queries which calls eg. buggy functions with dead loops...
3
by: adsheehan | last post by:
I have been reading many of the posting on the GIL and impact on threading etc. I have found is confusing and would welcome some clarity on this. I understand that embedding the interpreter in a...
5
by: Dylan Parry | last post by:
Hi, I am trying to debug some queries that are being generated by an external program, and I have no way of finding out what the actual syntax of the query is from within the program itself....
5
by: Sune | last post by:
Hi all, I want to make data stored in-memory (not disk) available to several processes. My concern is that poorly written C applications with dangling pointers may(will) damage the data in this...
3
by: Tom | last post by:
I have a PHP 4.4.7, MySql 5.0 and I.E. 7. My PHP queries the database and renders the info into html forms. Submitting the forms manipulates the data. Now, the problem is that the browsers are...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.