X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AE77CD1B20E for ; Thu, 17 Jun 2004 05:00:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64898-08 for ; Thu, 17 Jun 2004 08:00:39 +0000 (GMT) Received: from web12702.mail.yahoo.com (web12702.mail.yahoo.com [216.136.173.239]) by svr1.postgresql.org (Postfix) with SMTP id 26DBDD1B1CD for ; Thu, 17 Jun 2004 05:00:35 -0300 (ADT) Message-ID: <20040617080036.2289.qmail@web12702.mail.yahoo.com> Received: from [211.95.204.100] by web12702.mail.yahoo.com via HTTP; Thu, 17 Jun 2004 01:00:36 PDT Date: Thu, 17 Jun 2004 01:00:36 -0700 (PDT) From: Subject: Re: Listing all open sessions/connections/XAs ? To: pgsql-admin@postgresql.org In-Reply-To: <20040616193851.95910.qmail@web12704.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.4 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME, RCVD_IN_RFCI X-Spam-Level: X-Archive-Number: 200406/216 X-Sequence-Number: 13876 Hello, I think pg_stat_activity table may show me what I need. However, even though I have 'stats_command_string = true' property in postgresql.conf (and I restarted postmaster), I do not see the 'current_query' in pg_stat_activity table: simpydb=> select * from pg_stat_activity ; datid | datname | procpid | usesysid | usename | current_query -------+---------+---------+----------+---------+--------------- 16976 | simpydb | 31008 | 100 | otis | 16976 | simpydb | 26126 | 100 | otis | (2 rows) But, I see that these 2 connections are stuck in a transaction: > ps auxwww| grep post postgres 31008 0.0 0.4 11372 4864 ? S Jun16 0:13 postgres: otis mydb 127.0.0.1 idle in transaction postgres 26126 0.0 0.4 11560 4936 ? S 02:46 0:01 postgres: otis mydb 127.0.0.1 idle in transaction How come I can't see that 'current_query'? Does that mean that the DB connection is stuck inside a transaction, but there is no actual SQL being executed? Would that be an equivalent of: BEGIN TRANSACTION ? Is there a way to see the transaction associated with a connection that is in that 'idle in transaction' state? Thank you, Otis --- ogjunk-pgjedan@yahoo.com wrote: > Hello, > > Occasionally I see that my (web) app leaves some DB connections open, > so they look like this to `ps': > > postgres: username dbname 127.0.0.1 idle in transaction > > This results in my DB connection pool getting exhausted every so > often. > I need to track the source of this problem. > > Is there a way to see the SQL (or any other information) associated > with a connection/session/transaction that caused my DB connection to > remain open? > > I know MS SQL Server has something like that, and so does Oracle, but > I > haven't seen this mentioned in the PostgreSQL Admin Guide. > > I have PG 7.3.4. > > Thanks, > Otis > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >