We had an outage caused by transaction wraparound. And yes, one of the
first things I did on this site was check that we didn't have any
databases that were in danger of wraparound.
However since then we added a monitoring job that used a temporary
table with ON COMMIT DELETE ROWS. Since it was a simple monitoring job
it stayed connected to the database and used this small temporary
table for a very long period of time.
The temporary table never got vacuumed by autovacuum and never by the
monitoring job (since it was being truncated on every transaction why
would it need to be vacuumed...).
We've been around this bush before. Tom added orphaned table
protection to autovacuum precisely because temporary tables can cause
the datfrozenxid to get held back indefinitely. Then Michael Paquier
and Tsunakawa Takayuki both found it worth making this more
aggressive.
But none of that helped as the temporary schema was still in use so
they were not considered "orphaned" temp tables at all.
I think we need to add some warnings to autovacuum when it detects
*non* orphaned temporary tables that are older than the freeze
threshold.
However in the case of ON COMMIT DELETE ROWS we can do better. Why not
just reset the relfrozenxid and other stats as if the table was
freshly created when it's truncated?
I put together this quick patch to check the idea and it seems to
integrate fine in the code. I'm not sure about a few points but I
don't think they're showstoppers.
1) Should we update relpages and reltuples. I think so but an argument
could be made that people might be depending on running analyze once
when the data is loaded and then not having to run analyze on every
data load.
2) adding the dependency on heapam.h to heap.c makes sense because of
heap_inplace_update bt it may be a bit annoying because I suspect
that's a useful sanity check that the tableam stuff hasn't been
bypassed
3) I added a check to the regression tests but I'm not sure it's a
good idea to actually commit this. It could fail if there's a parallel
transaction going on and even moving the test to the serial schedule
might not guarantee that never happens due to autovacuum running
analyze?
I didn't actually add the warning to autovacuum yet.
--
greg
From 76eb00c43fba2a293dc4a079307e675e0eeaff06 Mon Sep 17 00:00:00 2001
From: Greg Stark <st...@intelerad.com>
Date: Sun, 8 Nov 2020 11:54:50 -0500
Subject: [PATCH] update relfrozenxmin when truncating temp tables
---
src/backend/catalog/heap.c | 45 ++++++++++++++++++++++++++++++++++++++
src/test/regress/expected/temp.out | 21 ++++++++++++++++++
src/test/regress/parallel_schedule | 9 +++++---
src/test/regress/sql/temp.sql | 19 ++++++++++++++++
4 files changed, 91 insertions(+), 3 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 4cd7d76..ffe36bb 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/htup_details.h"
#include "access/multixact.h"
#include "access/relation.h"
@@ -3277,6 +3278,48 @@ RelationTruncateIndexes(Relation heapRelation)
}
/*
+ * Reset the relfrozenxid and other stats to the same values used when
+ * creating tables. This is used after non-transactional truncation.
+ *
+ * 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;
+
+ /* 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 = 0;
+ pgcform->relallvisible = 0;
+ pgcform->relfrozenxid = RecentXmin;
+ pgcform->relminmxid = GetOldestMultiXactId();
+
+ heap_inplace_update(classRel, ctup);
+
+ table_close(classRel, RowExclusiveLock);
+}
+
+/*
* heap_truncate
*
* This routine deletes all data within all the specified relations.
@@ -3340,6 +3383,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);
@@ -3351,6 +3395,7 @@ heap_truncate_one_rel(Relation rel)
Relation toastrel = table_open(toastrelid, AccessExclusiveLock);
table_relation_nontransactional_truncate(toastrel);
+ ResetVacStats(rel);
RelationTruncateIndexes(toastrel);
/* keep the lock... */
table_close(toastrel, NoLock);
diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out
index a5b3ed3..1fee552 100644
--- a/src/test/regress/expected/temp.out
+++ b/src/test/regress/expected/temp.out
@@ -83,6 +83,27 @@ SELECT * FROM temptest;
(0 rows)
DROP TABLE temptest;
+-- Test that ON COMMIT DELETE ROWS resets the relfrozenxid when the
+-- table is truncated. This requires this test not be run in parallel
+-- with other tests as concurrent transactions will hold back the
+-- globalxmin
+CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
+SELECT relpages, reltuples, relallvisible, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset old_
+BEGIN;
+INSERT INTO temptest (select generate_series(1,1000));
+ANALYZE temptest; -- update relpages, reltuples
+COMMIT;
+SELECT relpages, reltuples, relallvisible, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset new_
+SELECT :old_relpages = :new_relpages AS pages_reset,
+ :old_reltuples = :new_reltuples AS tuples_reset,
+ :old_relallvisible = :new_relallvisible AS allvisible_reset,
+ :old_relfrozenxid <> :new_relfrozenxid AS frozenxid_advanced;
+ pages_reset | tuples_reset | allvisible_reset | frozenxid_advanced
+-------------+--------------+------------------+--------------------
+ t | t | t | t
+(1 row)
+
+DROP TABLE temptest;
-- Test ON COMMIT DROP
BEGIN;
CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index ae89ed7..6d42127 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -104,10 +104,13 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath
# ----------
# Another group of parallel tests
-# NB: temp.sql does a reconnect which transiently uses 2 connections,
-# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+
+# Run temp by itself so it can verify relfrozenxid advances when
+# truncating temp tables (and because it does a reconnect which may
+# transiently use two connections)
+test: temp
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/temp.sql b/src/test/regress/sql/temp.sql
index 424d12b..5f0c39b 100644
--- a/src/test/regress/sql/temp.sql
+++ b/src/test/regress/sql/temp.sql
@@ -79,6 +79,25 @@ SELECT * FROM temptest;
DROP TABLE temptest;
+-- Test that ON COMMIT DELETE ROWS resets the relfrozenxid when the
+-- table is truncated. This requires this test not be run in parallel
+-- with other tests as concurrent transactions will hold back the
+-- globalxmin
+CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
+
+SELECT relpages, reltuples, relallvisible, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset old_
+BEGIN;
+INSERT INTO temptest (select generate_series(1,1000));
+ANALYZE temptest; -- update relpages, reltuples
+COMMIT;
+SELECT relpages, reltuples, relallvisible, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset new_
+SELECT :old_relpages = :new_relpages AS pages_reset,
+ :old_reltuples = :new_reltuples AS tuples_reset,
+ :old_relallvisible = :new_relallvisible AS allvisible_reset,
+ :old_relfrozenxid <> :new_relfrozenxid AS frozenxid_advanced;
+
+DROP TABLE temptest;
+
-- Test ON COMMIT DROP
BEGIN;
--
1.8.3.1
