Re: [HACKERS] WAL logging freezing - Mailing list pgsql-patches

On Tue, 2006-10-31 at 11:04 -0500, Tom Lane wrote:

> It seems that we're converging on the conclusion that not truncating
> clog early is the least bad alternative.  This has the advantage of
> making things a lot simpler --- we won't need to track minxid at all.
> Allow me to summarize what I think has to happen:
>
> * VACUUM will determine a freeze cutoff XID the same way it does now,
> except that instead of using a hard-wired freeze window of 1G
> transactions, we'll either reduce the window to (say) 100M transactions
> or provide a GUC variable that can be adjusted over some reasonable
> range.
>
> * All XIDs present in the table that are older than the cutoff XID will
> be replaced by FrozenXid or InvalidXid as required, and such actions
> will be WAL-logged.  (I think we need to consider all 3 of xmin, xmax,
> and xvac here.)
>
> * On successful completion, the cutoff XID is stored in
> pg_class.relvacuumxid, and pg_database.datvacuumxid is updated
> if appropriate.  (The minxid columns are now useless, but unless there
> is another reason to force initdb before 8.2, I'm inclined to leave them
> there and unused.  We can remove 'em in 8.3.)
>
> * pg_clog is truncated according to the oldest pg_database.datvacuumxid.
> We should WAL-log this action, because replaying such an entry will
> allow a PITR slave to truncate its own clog and thereby avoid wraparound
> conflicts.  Note that we no longer need a checkpoint before truncating
> --- what we need is XLogFlush, instead.  ("WAL before data")
>
> These changes get us back into the regime where the hint bits truly are
> hints, because the underlying pg_clog data is still there, both in a
> master database and in a PITR slave.  So we don't need to worry about
> WAL-logging hint bits.  We also avoid needing any flushes/fsyncs or
> extra checkpoints.  The added WAL volume should be pretty minimal,
> because only tuples that have gone untouched for a long time incur extra
> work.  The added storage space for pg_clog could be annoying for a small
> database, but reducing the freeze window ameliorates that objection.

Complete agreement, nice summary.

Do we need another GUC? I thought your observation about a PITR slave
having that set lower than its master still remains unresolved. Perhaps
we should do that by pg_config_manual.h, so its much less likely that
the two would be out of step?

Thanks to Heikki, for spotting the original bug before it was reported
in production.

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com