Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 1B9B19FB9D5 for ; Wed, 16 May 2007 19:46:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 48653-07 for ; Wed, 16 May 2007 19:46:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id AE3CE9FB8A6 for ; Wed, 16 May 2007 19:46:39 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-217-243.bk12-dsl.surnet.cl [201.221.217.243]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l4GMkWbF029959; Wed, 16 May 2007 15:46:37 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 318DE32A13; Wed, 16 May 2007 18:46:27 -0400 (CLT) Date: Wed, 16 May 2007 18:46:27 -0400 From: Alvaro Herrera To: Tom Lane Cc: Heikki Linnakangas , Chris Browne , pgsql-patches@postgresql.org Subject: Re: [DOCS] Autovacuum and XID wraparound Message-ID: <20070516224627.GP4582@alvh.no-ip.org> References: <23362.1179292856@sss.pgh.pa.us> <20070516124420.GA4582@alvh.no-ip.org> <464B0E5A.1070807@enterprisedb.com> <6549.1179344646@sss.pgh.pa.us> <464B60A7.3070106@enterprisedb.com> <7145.1179347418@sss.pgh.pa.us> <464B6B08.6030609@enterprisedb.com> <7460.1179349272@sss.pgh.pa.us> <20070516210531.GL4582@alvh.no-ip.org> <7667.1179350456@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="W/nzBZO5zC0uMSeA" Content-Disposition: inline In-Reply-To: <7667.1179350456@sss.pgh.pa.us> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Wed, 16 May 2007 15:46:39 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/259 X-Sequence-Number: 2359 --W/nzBZO5zC0uMSeA Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Tom Lane wrote: > Alvaro Herrera writes: > > Tom Lane wrote: > >> Heikki Linnakangas writes: > >>> How about freezing anything older than vacuum_freeze_min_age, just like > >>> VACUUM does? > >> > >> I suppose that'd be OK, but is it likely to be worth the trouble? > > > I think so, because it means that people using CLUSTER to keep the size > > of tables in line instead of VACUUM, would not need the otherwise > > mandatory VACUUM. > > Fair enough. Who will fix the already-applied patch? Here is my proposed patch. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support --W/nzBZO5zC0uMSeA Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="cluster-freeze-fix.patch" Index: src/backend/access/heap/rewriteheap.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/access/heap/rewriteheap.c,v retrieving revision 1.4 diff -c -p -r1.4 rewriteheap.c *** src/backend/access/heap/rewriteheap.c 16 May 2007 16:36:56 -0000 1.4 --- src/backend/access/heap/rewriteheap.c 16 May 2007 22:34:18 -0000 *************** typedef struct RewriteStateData *** 123,128 **** --- 123,130 ---- bool rs_use_wal; /* must we WAL-log inserts? */ TransactionId rs_oldest_xmin; /* oldest xmin used by caller to * determine tuple visibility */ + TransactionId rs_freeze_xid; /* Xid that will be used as freeze + * cutoff point */ MemoryContext rs_cxt; /* for hash tables and entries and * tuples in them */ HTAB *rs_unresolved_tups; /* unmatched A tuples */ *************** static void raw_heap_insert(RewriteState *** 171,176 **** --- 173,179 ---- * * new_heap new, locked heap relation to insert tuples to * oldest_xmin xid used by the caller to determine which tuples are dead + * freeze_xid xid before which tuples will be frozen * use_wal should the inserts to the new heap be WAL-logged? * * Returns an opaque RewriteState, allocated in current memory context, *************** static void raw_heap_insert(RewriteState *** 178,184 **** */ RewriteState begin_heap_rewrite(Relation new_heap, TransactionId oldest_xmin, ! bool use_wal) { RewriteState state; MemoryContext rw_cxt; --- 181,187 ---- */ RewriteState begin_heap_rewrite(Relation new_heap, TransactionId oldest_xmin, ! TransactionId freeze_xid, bool use_wal) { RewriteState state; MemoryContext rw_cxt; *************** begin_heap_rewrite(Relation new_heap, Tr *** 206,211 **** --- 209,215 ---- state->rs_buffer_valid = false; state->rs_use_wal = use_wal; state->rs_oldest_xmin = oldest_xmin; + state->rs_freeze_xid = freeze_xid; state->rs_cxt = rw_cxt; /* Initialize hash tables used to track update chains */ *************** raw_heap_insert(RewriteState state, Heap *** 538,544 **** OffsetNumber newoff; HeapTuple heaptup; ! heap_freeze_tuple(tup->t_data, state->rs_oldest_xmin, InvalidBuffer); /* * If the new tuple is too big for storage or contains already toasted --- 542,548 ---- OffsetNumber newoff; HeapTuple heaptup; ! heap_freeze_tuple(tup->t_data, state->rs_freeze_xid, InvalidBuffer); /* * If the new tuple is too big for storage or contains already toasted Index: src/backend/commands/cluster.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/commands/cluster.c,v retrieving revision 1.159 diff -c -p -r1.159 cluster.c *** src/backend/commands/cluster.c 8 Apr 2007 01:26:28 -0000 1.159 --- src/backend/commands/cluster.c 16 May 2007 22:34:24 -0000 *************** *** 29,34 **** --- 29,35 ---- #include "catalog/namespace.h" #include "catalog/toasting.h" #include "commands/cluster.h" + #include "commands/vacuum.h" #include "miscadmin.h" #include "storage/procarray.h" #include "utils/acl.h" *************** copy_heap_data(Oid OIDNewHeap, Oid OIDOl *** 657,662 **** --- 658,664 ---- HeapTuple tuple; bool use_wal; TransactionId OldestXmin; + TransactionId FreezeXid; RewriteState rwstate; /* *************** copy_heap_data(Oid OIDNewHeap, Oid OIDOl *** 688,698 **** /* use_wal off requires rd_targblock be initially invalid */ Assert(NewHeap->rd_targblock == InvalidBlockNumber); ! /* Get the cutoff xmin we'll use to weed out dead tuples */ ! OldestXmin = GetOldestXmin(OldHeap->rd_rel->relisshared, true); /* Initialize the rewrite operation */ ! rwstate = begin_heap_rewrite(NewHeap, OldestXmin, use_wal); /* * Scan through the OldHeap in OldIndex order and copy each tuple into the --- 690,705 ---- /* use_wal off requires rd_targblock be initially invalid */ Assert(NewHeap->rd_targblock == InvalidBlockNumber); ! /* ! * compute xids used to freeze and weed out dead tuples. We use -1 ! * freeze_min_age to avoid having CLUSTER freeze tuples earlier than ! * a plain VACUUM would. ! */ ! vacuum_set_xid_limits(-1, OldHeap->rd_rel->relisshared, ! &OldestXmin, &FreezeXid); /* Initialize the rewrite operation */ ! rwstate = begin_heap_rewrite(NewHeap, OldestXmin, FreezeXid, use_wal); /* * Scan through the OldHeap in OldIndex order and copy each tuple into the Index: src/backend/commands/vacuum.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/commands/vacuum.c,v retrieving revision 1.350 diff -c -p -r1.350 vacuum.c *** src/backend/commands/vacuum.c 16 Apr 2007 18:29:50 -0000 1.350 --- src/backend/commands/vacuum.c 16 May 2007 22:34:47 -0000 *************** get_rel_oids(List *relids, const RangeVa *** 566,572 **** * vacuum_set_xid_limits() -- compute oldest-Xmin and freeze cutoff points */ void ! vacuum_set_xid_limits(VacuumStmt *vacstmt, bool sharedRel, TransactionId *oldestXmin, TransactionId *freezeLimit) { --- 566,572 ---- * vacuum_set_xid_limits() -- compute oldest-Xmin and freeze cutoff points */ void ! vacuum_set_xid_limits(int freeze_min_age, bool sharedRel, TransactionId *oldestXmin, TransactionId *freezeLimit) { *************** vacuum_set_xid_limits(VacuumStmt *vacstm *** 588,599 **** Assert(TransactionIdIsNormal(*oldestXmin)); /* ! * Determine the minimum freeze age to use: as specified in the vacstmt, * or vacuum_freeze_min_age, but in any case not more than half * autovacuum_freeze_max_age, so that autovacuums to prevent XID * wraparound won't occur too frequently. */ ! freezemin = vacstmt->freeze_min_age; if (freezemin < 0) freezemin = vacuum_freeze_min_age; freezemin = Min(freezemin, autovacuum_freeze_max_age / 2); --- 588,599 ---- Assert(TransactionIdIsNormal(*oldestXmin)); /* ! * Determine the minimum freeze age to use: as specified by the caller, * or vacuum_freeze_min_age, but in any case not more than half * autovacuum_freeze_max_age, so that autovacuums to prevent XID * wraparound won't occur too frequently. */ ! freezemin = freeze_min_age; if (freezemin < 0) freezemin = vacuum_freeze_min_age; freezemin = Min(freezemin, autovacuum_freeze_max_age / 2); *************** full_vacuum_rel(Relation onerel, VacuumS *** 1154,1160 **** i; VRelStats *vacrelstats; ! vacuum_set_xid_limits(vacstmt, onerel->rd_rel->relisshared, &OldestXmin, &FreezeLimit); /* --- 1154,1160 ---- i; VRelStats *vacrelstats; ! vacuum_set_xid_limits(vacstmt->freeze_min_age, onerel->rd_rel->relisshared, &OldestXmin, &FreezeLimit); /* Index: src/backend/commands/vacuumlazy.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v retrieving revision 1.88 diff -c -p -r1.88 vacuumlazy.c *** src/backend/commands/vacuumlazy.c 30 Apr 2007 03:23:48 -0000 1.88 --- src/backend/commands/vacuumlazy.c 16 May 2007 22:34:55 -0000 *************** lazy_vacuum_rel(Relation onerel, VacuumS *** 158,164 **** else elevel = DEBUG2; ! vacuum_set_xid_limits(vacstmt, onerel->rd_rel->relisshared, &OldestXmin, &FreezeLimit); vacrelstats = (LVRelStats *) palloc0(sizeof(LVRelStats)); --- 158,164 ---- else elevel = DEBUG2; ! vacuum_set_xid_limits(vacstmt->freeze_min_age, onerel->rd_rel->relisshared, &OldestXmin, &FreezeLimit); vacrelstats = (LVRelStats *) palloc0(sizeof(LVRelStats)); Index: src/include/access/rewriteheap.h =================================================================== RCS file: /cvsroot/pgsql/src/include/access/rewriteheap.h,v retrieving revision 1.1 diff -c -p -r1.1 rewriteheap.h *** src/include/access/rewriteheap.h 8 Apr 2007 01:26:33 -0000 1.1 --- src/include/access/rewriteheap.h 16 May 2007 22:35:05 -0000 *************** *** 20,29 **** typedef struct RewriteStateData *RewriteState; extern RewriteState begin_heap_rewrite(Relation NewHeap, ! TransactionId OldestXmin, bool use_wal); extern void end_heap_rewrite(RewriteState state); extern void rewrite_heap_tuple(RewriteState state, HeapTuple oldTuple, ! HeapTuple newTuple); extern void rewrite_heap_dead_tuple(RewriteState state, HeapTuple oldTuple); #endif /* REWRITE_HEAP_H */ --- 20,30 ---- typedef struct RewriteStateData *RewriteState; extern RewriteState begin_heap_rewrite(Relation NewHeap, ! TransactionId OldestXmin, TransactionId FreezeXid, ! bool use_wal); extern void end_heap_rewrite(RewriteState state); extern void rewrite_heap_tuple(RewriteState state, HeapTuple oldTuple, ! HeapTuple newTuple); extern void rewrite_heap_dead_tuple(RewriteState state, HeapTuple oldTuple); #endif /* REWRITE_HEAP_H */ Index: src/include/commands/vacuum.h =================================================================== RCS file: /cvsroot/pgsql/src/include/commands/vacuum.h,v retrieving revision 1.70 diff -c -p -r1.70 vacuum.h *** src/include/commands/vacuum.h 13 Mar 2007 00:33:43 -0000 1.70 --- src/include/commands/vacuum.h 16 May 2007 22:35:08 -0000 *************** extern void vac_update_relstats(Oid reli *** 119,125 **** double num_tuples, bool hasindex, TransactionId frozenxid); ! extern void vacuum_set_xid_limits(VacuumStmt *vacstmt, bool sharedRel, TransactionId *oldestXmin, TransactionId *freezeLimit); extern void vac_update_datfrozenxid(void); --- 119,125 ---- double num_tuples, bool hasindex, TransactionId frozenxid); ! extern void vacuum_set_xid_limits(int freeze_min_age, bool sharedRel, TransactionId *oldestXmin, TransactionId *freezeLimit); extern void vac_update_datfrozenxid(void); --W/nzBZO5zC0uMSeA--