From 48096d0f3d6f207cdcdf12ae3b44e34bf8615f96 Mon Sep 17 00:00:00 2001 From: Greg Stark Date: Thu, 31 Mar 2022 15:49:19 -0400 Subject: [PATCH v7 2/3] Update relfrozenxmin when truncating temp tables Make ON COMMIT DELETE ROWS reset relfrozenxmin and other table stats to the same values used in initial table creation. Otherwise even typical short-lived transactions in long-lived sessions using temporary tables can easily cause them to reach transaction wraparound and autovacuum cannot come to the rescue for temporary tables. Also optimize the relminmxid used for for temporary tables to be our own oldest MultiXactId instead of the globally oldest one. This avoids the expensive calculation of the latter on every transaction commit. This code path is also used by truncation of tables created within the same transaction. However we skip initializing relminmxid as its expensive to calculate and these tables can eventually be vacuumed by autovacuum anyways. --- src/backend/access/heap/heapam_handler.c | 13 ++++- src/backend/access/transam/multixact.c | 15 +++++ src/backend/catalog/heap.c | 72 ++++++++++++++++++++++++ src/include/access/multixact.h | 1 + 4 files changed, 99 insertions(+), 2 deletions(-) diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index 444f027149..bca8f22250 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -587,9 +587,18 @@ heapam_relation_set_new_filenode(Relation rel, * could reuse values from their local cache, so we are careful to * consider all currently running multis. * - * XXX this could be refined further, but is it worth the hassle? + * In the case of temporary tables we can refine this slightly and use a + * our own oldest visible MultiXactId. This is also cheaper to calculate + * which is nice since temporary tables might be getting created often. */ - *minmulti = GetOldestMultiXactId(); + if (persistence == RELPERSISTENCE_TEMP) + { + *minmulti = GetOurOldestMultiXactId(); + } + else + { + *minmulti = GetOldestMultiXactId(); + } srel = RelationCreateStorage(*newrnode, persistence, true); diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c index 8f7d12950e..5b07b6ef22 100644 --- a/src/backend/access/transam/multixact.c +++ b/src/backend/access/transam/multixact.c @@ -2491,6 +2491,21 @@ ExtendMultiXactMember(MultiXactOffset offset, int nmembers) } } +/* + * GetOurOldestMultiXactId + * + * Expose the oldest MultiXactId possibly seen as live by *this* + * transaction. This is mainly useful for initializing relminmxid on temp + * tables since they can't been modified by other transactions. + */ + +MultiXactId +GetOurOldestMultiXactId(void) +{ + MultiXactIdSetOldestVisible(); + return OldestVisibleMXactId[MyBackendId]; +} + /* * GetOldestMultiXactId * diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 1803194db9..b5165ef495 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -30,6 +30,7 @@ #include "postgres.h" #include "access/genam.h" +#include "access/heapam.h" #include "access/multixact.h" #include "access/relation.h" #include "access/table.h" @@ -72,6 +73,7 @@ #include "utils/fmgroids.h" #include "utils/inval.h" #include "utils/lsyscache.h" +#include "utils/snapmgr.h" #include "utils/syscache.h" @@ -2988,6 +2990,66 @@ RelationTruncateIndexes(Relation heapRelation) } } +/* + * Reset the relfrozenxid and other stats to the same values used when + * creating tables. This is used after non-transactional truncation. + * + * Doing this reduces the need for long-running programs to vacuum their own + * temporary tables (since they're not covered by autovacuum) at least in the + * case where they're ON COMMIT DELETE ROWS. + * + * see also src/backend/commands/vacuum.c vac_update_relstats() + * also see AddNewRelationTuple() above + */ + +static void +ResetVacStats(Relation rel) +{ + HeapTuple ctup; + Form_pg_class pgcform; + Relation classRel; + + /* Ensure RecentXmin is valid -- it almost certainly is but regression + * tests turned up an unlikely corner case where it might not be */ + if (!FirstSnapshotSet) + (void)GetLatestSnapshot(); + Assert(FirstSnapshotSet); + + /* Fetch a copy of the tuple to scribble on */ + classRel = table_open(RelationRelationId, RowExclusiveLock); + ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(RelationGetRelid(rel))); + if (!HeapTupleIsValid(ctup)) + elog(ERROR, "pg_class entry for relid %u vanished during truncation", + RelationGetRelid(rel)); + pgcform = (Form_pg_class) GETSTRUCT(ctup); + + /* + * Update relfrozenxid + */ + + pgcform->relpages = 0; + pgcform->reltuples = -1; + pgcform->relallvisible = 0; + pgcform->relfrozenxid = RecentXmin; + + /* If this is a temp table we can use the oldest mxid visible from this + * transaction. Temp tables are the important case since autovacuum can't + * get them. + * + * If it isn't it's moderately expensive to calculate the global minimum + * so just leave it for autovacuum to deal with. + */ + + if (pgcform->relpersistence == RELPERSISTENCE_TEMP) + { + pgcform->relminmxid = GetOurOldestMultiXactId(); + } + + heap_inplace_update(classRel, ctup); + + table_close(classRel, RowExclusiveLock); +} + /* * heap_truncate * @@ -2996,6 +3058,14 @@ RelationTruncateIndexes(Relation heapRelation) * This is not transaction-safe! There is another, transaction-safe * implementation in commands/tablecmds.c. We now use this only for * ON COMMIT truncation of temporary tables, where it doesn't matter. + * + * Or whenever a table's relfilenode was created within the same transaction + * such as when the table was created or truncated (normally) within this + * transaction. + * + * The correctness of this code depends on the fact that the table creation or + * truncation would be rolled back *including* the insert/update to the + * pg_class row that we update in place here. */ void heap_truncate(List *relids) @@ -3052,6 +3122,7 @@ heap_truncate_one_rel(Relation rel) /* Truncate the underlying relation */ table_relation_nontransactional_truncate(rel); + ResetVacStats(rel); /* If the relation has indexes, truncate the indexes too */ RelationTruncateIndexes(rel); @@ -3063,6 +3134,7 @@ heap_truncate_one_rel(Relation rel) Relation toastrel = table_open(toastrelid, AccessExclusiveLock); table_relation_nontransactional_truncate(toastrel); + ResetVacStats(toastrel); RelationTruncateIndexes(toastrel); /* keep the lock... */ table_close(toastrel, NoLock); diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h index a5600a320a..010eb09a1e 100644 --- a/src/include/access/multixact.h +++ b/src/include/access/multixact.h @@ -139,6 +139,7 @@ extern void MultiXactGetCheckptMulti(bool is_shutdown, MultiXactId *oldestMulti, Oid *oldestMultiDB); extern void CheckPointMultiXact(void); +extern MultiXactId GetOurOldestMultiXactId(void); extern MultiXactId GetOldestMultiXactId(void); extern void TruncateMultiXact(MultiXactId oldestMulti, Oid oldestMultiDB); extern void MultiXactSetNextMXact(MultiXactId nextMulti, -- 2.36.1