Re: pg_xlog is getting bigger

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

No, you're thinking about an Oracle-style WAL implementation. We don't
do rollback; both tuple versions are kept around until VACUUM decides it
can reclaim one or the other. WAL only need be kept back to the latest
checkpoint, even if there are open transactions that are much older.

regards, tom lane