From 763d64c023825ead0534bf3a08a06e7aec08ba63 Mon Sep 17 00:00:00 2001 From: Greg Stark Date: Thu, 31 Mar 2022 15:50:02 -0400 Subject: [PATCH v6 3/3] Add test for truncating temp tables advancing relfrozenxid This test depends on other transactions not running at the same time so that relfrozenxid can advance so it has to be moved to its own schedule. --- src/test/regress/expected/temp.out | 37 ++++++++++++++++++++++++++++++ src/test/regress/parallel_schedule | 10 +++++--- src/test/regress/sql/temp.sql | 30 ++++++++++++++++++++++++ 3 files changed, 74 insertions(+), 3 deletions(-) diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out index a5b3ed34a3..244b868ef7 100644 --- a/src/test/regress/expected/temp.out +++ b/src/test/regress/expected/temp.out @@ -82,6 +82,43 @@ 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 text) ON COMMIT DELETE ROWS; +SELECT reltoastrelid, reltoastrelid::regclass AS relname FROM pg_class where oid = 'temptest'::regclass \gset toast_ +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset old_ +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = :toast_reltoastrelid \gset toast_old_ +BEGIN; +INSERT INTO temptest (select repeat('foobar',generate_series(1,1000))); +ANALYZE temptest; -- update relpages, reltuples +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset temp_ +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = :toast_reltoastrelid \gset toast_temp_ +COMMIT; +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset new_ +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = :toast_reltoastrelid \gset toast_new_ +-- make sure relpages and reltuple match a newly created table and +-- relfrozenxid is advanced +SELECT :old_relpages <> :temp_relpages AS pages_analyzed, + :old_relpages = :new_relpages AS pages_reset, + :old_reltuples <> :temp_reltuples AS tuples_analyzed, + :old_reltuples = :new_reltuples AS tuples_reset, + :old_relfrozenxid <> :new_relfrozenxid AS frozenxid_advanced; + pages_analyzed | pages_reset | tuples_analyzed | tuples_reset | frozenxid_advanced +----------------+-------------+-----------------+--------------+-------------------- + t | t | t | t | t +(1 row) + +-- The toast table can't be analyzed so relpages and reltuples can't +-- be tested easily make sure frozenxid is advanced +SELECT :toast_old_relfrozenxid <> :toast_new_relfrozenxid AS frozenxid_advanced; + frozenxid_advanced +-------------------- + t +(1 row) + DROP TABLE temptest; -- Test ON COMMIT DROP BEGIN; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 5030d19c03..fd86ce5b5c 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,10 +116,14 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson # ---------- # Another group of parallel tests # with depends on create_misc -# 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 this alone because it transiently uses 2 connections and also +# tests relfrozenxid advances when truncating temp tables +# ---------- +test: temp # ---------- # Another group of parallel tests diff --git a/src/test/regress/sql/temp.sql b/src/test/regress/sql/temp.sql index 424d12b283..5f8647a8aa 100644 --- a/src/test/regress/sql/temp.sql +++ b/src/test/regress/sql/temp.sql @@ -79,6 +79,36 @@ 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 text) ON COMMIT DELETE ROWS; + +SELECT reltoastrelid, reltoastrelid::regclass AS relname FROM pg_class where oid = 'temptest'::regclass \gset toast_ +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset old_ +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = :toast_reltoastrelid \gset toast_old_ +BEGIN; +INSERT INTO temptest (select repeat('foobar',generate_series(1,1000))); +ANALYZE temptest; -- update relpages, reltuples +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset temp_ +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = :toast_reltoastrelid \gset toast_temp_ +COMMIT; +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = 'temptest'::regclass \gset new_ +SELECT relpages, reltuples, relfrozenxid FROM pg_class where oid = :toast_reltoastrelid \gset toast_new_ +-- make sure relpages and reltuple match a newly created table and +-- relfrozenxid is advanced +SELECT :old_relpages <> :temp_relpages AS pages_analyzed, + :old_relpages = :new_relpages AS pages_reset, + :old_reltuples <> :temp_reltuples AS tuples_analyzed, + :old_reltuples = :new_reltuples AS tuples_reset, + :old_relfrozenxid <> :new_relfrozenxid AS frozenxid_advanced; +-- The toast table can't be analyzed so relpages and reltuples can't +-- be tested easily make sure frozenxid is advanced +SELECT :toast_old_relfrozenxid <> :toast_new_relfrozenxid AS frozenxid_advanced; + +DROP TABLE temptest; + -- Test ON COMMIT DROP BEGIN; -- 2.35.1