Stuck processes, viewing the queries inside

Bryan Field-Elliot

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

Subscribe Reply

2981

Tom Lane

Bryan Field-Elliot <br*********@ne tmeme.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*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

Tom Lane

Bryan Field-Elliot <br*********@ne tmeme.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".

Nov 12 '05 #3

Similar topics

1630

Why don't processes go away?

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. When I look at my current activity in the database, sleeping processes keep accumulating for each of these queries. Why don't they go away? Should I care? Are

.NET Framework

1843

large data transfer between 2 processes

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 deamons which effectively execute requests. the PHP uses message queuing(1) to queue queries request to the daemons. but the answer may be quite large, larger than messages the message queueing service can handle.

PHP

23088

Multiple worker processes for a single application in a applicationpool

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 to a pool. If I assign only one application to a applicaton pool and have multiple worker processes assigned to that pool. Will my application be processed by many worker processes?

ASP.NET

2495

Spawning multiple processes at same time

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 this execution... we need immediate execution of all notification processes and would prefer to spawn multiple instances generating emails at one time. Solution ? I took a look at threading, and at first it looked to be the answer.

Visual Basic .NET

1374

How to kick out automatically stuck in queries

by: Együd 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 or something similar. Time to time I face such kind of problems which are solved automatically after some hours/days. Is there any options in config files or other settings to shorten this time?

PostgreSQL Database

1460

Clarity: GIL, processes and CPUs etc

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 C/C++ application limits it to one CPU. If the application is multi-threaded (system threads) in will not use additional CPUs as the interpreter is tied to one CPU courtesy of the GIL. True or False?

Python

1256

Viewing events as they happen?

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. This means that when the query fails I am left with only a very unhelpful message. So what I was wondering, was whether there is a way that I can view the actual queries that are sent to SQL server as they happen, or if a real-time solution...

Microsoft SQL Server

587

Avoid memory corruption in shared memory used by several processes?

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 memory segment if it is open to all, i.e. shared memory mapped into all processes memory area. I don't want to use TCP/IP client/server between the apps and a data store process due to the overhead.

C / C++

1993

Browser cache causing problem viewing php generated dynamic data

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 caching the dynamic pages. Once the data is updated, returning to the original viewing page shows the old data until you hit refresh. I know I can have my users adjust their internet file settings to "Check for newer versions of stored pages" to...

HTML / CSS

9715

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9595

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10099

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6869

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5536

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5675

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4314

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3836

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

3003

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General