pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: ogjunk-pgjedan@yahoo.com
To: pgsql-admin@postgresql.org
Subject: Re: Listing all open sessions/connections/XAs ?
Date: Thu, 17 Jun 2004 01:00:36 -0700 (PDT)
Message-ID: <20040617080036.2289.qmail@web12702.mail.yahoo.com> (raw)
In-Reply-To: <20040616193851.95910.qmail@web12704.mail.yahoo.com>

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
<don't do anything here and never END/COMMON/ROLLBACK the XA>

?

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
> 




view thread (2+ messages)

Message-ID: <20040617080036.2289.qmail@web12702.mail.yahoo.com>
Permalink:  ../20040617080036.2289.qmail@web12702.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040617080036.2289.qmail@web12702.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: ogjunk-pgjedan@yahoo.com
  Subject: Re: Listing all open sessions/connections/XAs ?
  In-Reply-To: <20040617080036.2289.qmail@web12702.mail.yahoo.com>
  References: <20040616193851.95910.qmail@web12704.mail.yahoo.com> <20040617080036.2289.qmail@web12702.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox