Re: pg_xlog is getting bigger

On 12/19/2012 04:12 PM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com