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. It only takes a minute to sign up.
Sign up to join this community
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!
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.
Beware that the state column is present only in PostgreSQL 9.2 and above.
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.
