Well the question is how long have those idle transactions been around?
Idle transactions shouldn't have anything to do with pg_xlog bloat. What causes xlog bloat is inability to release old WAL because either (a) we're not able to complete checkpoints, or (b) WAL archiving is enabled but malfunctioning, and the old WAL segments are being kept pending successful archiving.
Its obvious I am missing something important about WAL. Scenario: 1) Transaction is opened and say many UPDATEs are done. 2) This means there is now an old tuple and a new tuple for the previous row. 3) The transaction is not committed.
I assumed the WAL logs contained information necessary to either go forward to the new on commit or go back to the old on rollback. I further assumed the log segment(s) could not be released until either a commit/rollback was done.
At this point I figure I the above assumption is wrong or my understanding of <IDLE in TRANSACTION> is wrong or both!
Either (a) or (b) should result in bleating in the postmaster log.
I modified checkpoint_segment to 100 form 300 and then forced some CHECKPOINT and pg_switch_xlog() and now found that the pg_xlog file got almost 1 gb of space back.
