How can I get any statistics about "idle in transaction" connections to my PostgreSQL instance?

I want to get statistics about "idle in transaction" connections.

This statistics should be a part of performance tests results and it can reveal some开发者_Go百科 bugs in the tested system.

What can I use to gather this statistics?

I'm not sure what you mean with "statistics" but you can see all open connections (and their transaction state) by querying the system view pg_stat_activity:

http://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-VIEWS

In postgresql.conf set logging to

log_statement = 'all'                # none, ddl, mod, all

and check that

log_line_prefix='%t:%r:%a:%u@%d:[%p]: '

contains %p, which is the [process ID] of the SQL.

After a reload of the database settings, in the Table *pg_stat_activity* you can read the procpid column of the idle in transaction process.

Now you can grep through the logfiles to see what SQL has been executed on the connections before they became idle in transaction.