On Wed, Dec 19, 2012 at 7:52 PM, Adrian Klaver <adrian.kla...@gmail.com>wrote:
> On 12/19/2012 04:12 PM, Tom Lane wrote: > >> Adrian Klaver <adrian.kla...@gmail.com> writes: >> >>> 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. >> >> regards, tom lane >> >> >> > > -- > Adrian Klaver > adrian.kla...@gmail.com > 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.
