pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Listing all open sessions/connections/XAs ? 2+ messages / 1 participants [nested] [flat]
* Listing all open sessions/connections/XAs ? @ 2004-06-16 19:38 ogjunk-pgjedan@yahoo.com 2004-06-17 08:00 ` Re: Listing all open sessions/connections/XAs ? ogjunk-pgjedan@yahoo.com 0 siblings, 1 reply; 2+ messages in thread From: ogjunk-pgjedan@yahoo.com @ 2004-06-16 19:38 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Listing all open sessions/connections/XAs ? 2004-06-16 19:38 Listing all open sessions/connections/XAs ? ogjunk-pgjedan@yahoo.com @ 2004-06-17 08:00 ` ogjunk-pgjedan@yahoo.com 0 siblings, 0 replies; 2+ messages in thread From: ogjunk-pgjedan@yahoo.com @ 2004-06-17 08:00 UTC (permalink / raw) To: pgsql-admin 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 > ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-06-17 08:00 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-06-16 19:38 Listing all open sessions/connections/XAs ? ogjunk-pgjedan@yahoo.com 2004-06-17 08:00 ` ogjunk-pgjedan@yahoo.com
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