Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some performance issues.

What is a good way to determine if there are uncommitted transactions (bonus points if there is a way to know if the connection they're on is idle or not)?

Thanks very much!

share|improve this question
Have a look at pgtop. You may also hunt for rows showing "idle in transaction" in the output of ps aux. – dezso Apr 11 '13 at 5:20
@dezso - pgtop looks interesting; is there an equivalent for Windows? – Max Vernon Apr 11 '13 at 14:25
@MaxVernon there are some hints that it is supposed to work on Windows but haven't seen any concrete example. But it is still a (relatively) simple Perl project, so... And anyway, it works on the pg_stat* views. – dezso Apr 11 '13 at 15:20
up vote 7 down vote accepted

If you want to see how many idle connections you have that have an open transaction, you could use:

select * 
from pg_stat_activity
where (state = 'idle in transaction')
    and xact_start is not null;

This will provide a list of open connections that are in the idle state, that also have an open transaction.

Having said that, I cannot recreate an open connection in the idle state that has an open transaction. Perhaps someone else can provide details on how to do that.

share|improve this answer
Thanks very much! – Juan Carlos Coto Apr 11 '13 at 15:29
Beware that the state column is present only in 9.2. And for me it looks like the proper state is 'idle in transaction'. – dezso Apr 11 '13 at 16:29
I agree with dezso. – francs Apr 12 '13 at 2:01
state = 'idle' does not indicate an open transaction. – a_horse_with_no_name Dec 9 '14 at 17:23
I cannot recreate an open connection in the idle state that has an open transaction. Open two connections to the database, and type "begin;" in one. In the other run the above query, and you will have an idle in transaction. – X-Istence May 19 at 5:19

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.