Thread

[PATCH v1 12/12] prune_xid-hist: tests

Renan Alves Fonseca <renanfonseca@gmail.com> — 2025-05-30T14:17:22Z

---
 src/test/regress/sql/prune_xid_aux_check.sql | 29 +++++++++
 src/test/regress/sql/prune_xid_hist.sql      | 62 ++++++++++++++++++++
 2 files changed, 91 insertions(+)
 create mode 100644 src/test/regress/sql/prune_xid_aux_check.sql
 create mode 100644 src/test/regress/sql/prune_xid_hist.sql

diff --git a/src/test/regress/sql/prune_xid_aux_check.sql b/src/test/regress/sql/prune_xid_aux_check.sql
new file mode 100644
index 00000000000..da2797c0e4e
--- /dev/null
+++ b/src/test/regress/sql/prune_xid_aux_check.sql
@@ -0,0 +1,29 @@
+
+select pg_stat_force_next_flush();
+analyze :tt; select relpages as tt_relpages from pg_class where relname=:'tt' \gset
+
+select pg_stat_get_prune_xid_freqs(:'tt'::regclass)::text::text[];
+
+with estimate as (
+  select bounds, sum(freqs::int) over (order by bounds::int) estimated_cumsum
+  from
+    unnest( pg_stat_get_prune_xid_bounds(:'tt'::regclass)::text::text[])
+      with ordinality as t1(bounds,ord)
+  natural join
+    unnest( pg_stat_get_prune_xid_freqs(:'tt'::regclass)::text::text[])
+      with ordinality as t2(freqs,ord)
+  order by bounds)
+, pageinspect_vals as (
+  select prune_xid
+  from generate_series(1,:tt_relpages) as t(n),
+       page_header(get_raw_page(:'tt', n-1))
+  where prune_xid::text!='0')
+
+select bounds,
+       estimated_cumsum,
+       (select count(*) from pageinspect_vals where prune_xid::text::int<=bounds::int)
+from estimate
+where bounds::int>0
+order by bounds::int;
+
+
diff --git a/src/test/regress/sql/prune_xid_hist.sql b/src/test/regress/sql/prune_xid_hist.sql
new file mode 100644
index 00000000000..3f87afc8f85
--- /dev/null
+++ b/src/test/regress/sql/prune_xid_hist.sql
@@ -0,0 +1,62 @@
+-- test UPDATE
+create table t1(a int) with (autovacuum_enabled='off');
+select pg_stat_get_prune_xid_freqs('t1'::regclass);
+
+insert into t1 (select generate_series(1,10000));
+select pg_stat_get_prune_xid_freqs('t1'::regclass);
+
+update t1 set a=a+1 where a % 3 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 1 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 7 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 9 = 0;select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t1'::regclass);
+update t1 set a=a+1 where a % 3 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 1 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 7 = 0;select pg_stat_force_next_flush();
+update t1 set a=a+1 where a % 9 = 0;select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t1'::regclass);
+
+
+\set tt t1
+\ir prune_xid_aux_check.sql
+
+
+-- test DELETE
+create table t2(a int) with (autovacuum_enabled='off');
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+insert into t2 (select generate_series(1,10000));
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+delete from t2 where a%2=1;select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+\set tt t2
+\ir prune_xid_aux_check.sql
+
+delete from t2 where a%2=0;select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+insert into t2 (select generate_series(1,10000));
+delete from t2; select pg_stat_force_next_flush();
+select pg_stat_get_prune_xid_freqs('t2'::regclass);
+
+
+\set tt t2
+\ir prune_xid_aux_check.sql
+
+
+
+-- test VACUUM
+vacuum t1;
+\set tt t1
+\ir prune_xid_aux_check.sql
+
+vacuum t2;
+\set tt t2
+\ir prune_xid_aux_check.sql
+
+
+
+drop table t1;
+drop table t2;
-- 
2.47.0


--=-=-=--