Listing all open sessions/connections/XAs ?

[o***@yahoo.com]

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 ***@postgresql.org

[o***@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)

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

Hello,
Occasionally I see that my (web) app leaves some DB connections open,
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org