public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirill Reshke <[email protected]>
To: Melanie Plageman <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: Chao Li <[email protected]>
Cc: Xuneng Zhou <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
Date: Fri, 30 Jan 2026 14:59:57 +0500
Message-ID: <CALdSSPjnnXyJ3Ma6VTfYOT2KvOf_f6poMrPtAjNc5V2iFSE8Vw@mail.gmail.com> (raw)
In-Reply-To: <CALdSSPhFrQ4=N6yJ0N4pQ=RKjeSfU7N3wz7xpLTwsUjLc-6Tdg@mail.gmail.com>
References: <CABPTF7X1X810eT7hjRz9M69sjMv1Vui5gXQEGhqYXmkmywhHCQ@mail.gmail.com>
<CAAKRu_ZcK+ez71W7j+QzMo0gSafTsbGA3TU-fVSptw7yh41BEQ@mail.gmail.com>
<[email protected]>
<CAAKRu_ZCjHoRPfQ8AbMrFY8TOMCPAvZ0_m9SX7yg0edfTk45-g@mail.gmail.com>
<[email protected]>
<CAAKRu_a04jbDACwzRYwzDND31aPyf7Yvz9TAZrTr=+F5bK1aVA@mail.gmail.com>
<CALdSSPjcv25jmXm29X-MRWZBae6+HwcWfVH1PE8NfD=EMTnkAg@mail.gmail.com>
<CAAKRu_bwtBEzDwemyim1r6yYonw7FTyFr1HXG8vywCe-MdbPBQ@mail.gmail.com>
<[email protected]>
<CAAKRu_YQd=2KvomM+RHcpeDKj0bq+peJ=3W-fip+pkvzA-Jq9w@mail.gmail.com>
<7ib3sa55sapwjlaz4sijbiq7iezna27kjvvvar4dpgkmadml6t@gfpkkwmdnepx>
<CAAKRu_bs+gZ83QDacmBxunPvCGnXJ05hxP2BDPJ3BGwdbGRXzg@mail.gmail.com>
<[email protected]>
<CALdSSPjUUfdb5tL0hdH9m3PzRePDSFDBV-jfAj6BdzGE-8SBtw@mail.gmail.com>
<CAAKRu_ZnJiLaQD_pcTN=KGPHt3m-XirR+=Jj3OqURghPmu01yg@mail.gmail.com>
<CALdSSPhFrQ4=N6yJ0N4pQ=RKjeSfU7N3wz7xpLTwsUjLc-6Tdg@mail.gmail.com>
On Fri, 30 Jan 2026 at 14:25, Kirill Reshke <[email protected]> wrote:
>
> Sorry, I messed up my previous email.
>
> > create table test_vac_unmodified_heap(a int) with (autovacuum_enabled = false);
>
> Yes I did try this, but it does not help, because autovacuum runs on
> catalog relations, still causing fail.
>
> We cannot disable autovac globally in regression suite, so I propose
> to changes this to TAp test
>
FPA POC v1
I use 'autovacuum-worker-start' injection point to 'disable'
autovacuum until test is done
--
Best regards,
Kirill Reshke
Attachments:
[application/octet-stream] v1-0001-Reimplement-regression-tests-from-21796c267-as-TA.patch (8.0K, 2-v1-0001-Reimplement-regression-tests-from-21796c267-as-TA.patch)
download | inline diff:
From b0761ac76175b583f913ef2ff5994f467e194731 Mon Sep 17 00:00:00 2001
From: reshke <[email protected]>
Date: Fri, 30 Jan 2026 09:54:26 +0000
Subject: [PATCH v1] Reimplement regression tests from 21796c267 as TAP-test.
Regression test changes introduces in 21796c267 appears to be flakky
due to possible concurrent autovacuum activity. This actuvity can
allocate xids, preventing VACUUM FREEZE to actaully freeze page tuples
due to hilding oldest xmin.
So, reimplement this test as TAP-test, and prevent autovacuum workers
start with injection point.
This was actually detected by scorpion build animal.
Thanks to Alexander Lakhin for detailed report.
---
contrib/pg_visibility/Makefile | 4 +-
.../pg_visibility/expected/pg_visibility.out | 44 ------------
contrib/pg_visibility/meson.build | 4 ++
contrib/pg_visibility/sql/pg_visibility.sql | 20 ------
contrib/pg_visibility/t/003_vacuum_freeze.pl | 70 +++++++++++++++++++
5 files changed, 77 insertions(+), 65 deletions(-)
create mode 100644 contrib/pg_visibility/t/003_vacuum_freeze.pl
diff --git a/contrib/pg_visibility/Makefile b/contrib/pg_visibility/Makefile
index e5a74f32c48..7768bdf72d7 100644
--- a/contrib/pg_visibility/Makefile
+++ b/contrib/pg_visibility/Makefile
@@ -10,7 +10,9 @@ DATA = pg_visibility--1.1.sql pg_visibility--1.1--1.2.sql \
pg_visibility--1.0--1.1.sql
PGFILEDESC = "pg_visibility - page visibility information"
-EXTRA_INSTALL = contrib/pageinspect
+EXTRA_INSTALL=src/test/modules/injection_points contrib/pageinspect
+export enable_injection_points
+
REGRESS = pg_visibility
TAP_TESTS = 1
diff --git a/contrib/pg_visibility/expected/pg_visibility.out b/contrib/pg_visibility/expected/pg_visibility.out
index e10f1706015..09fa5933a35 100644
--- a/contrib/pg_visibility/expected/pg_visibility.out
+++ b/contrib/pg_visibility/expected/pg_visibility.out
@@ -1,5 +1,4 @@
CREATE EXTENSION pg_visibility;
-CREATE EXTENSION pageinspect;
--
-- recently-dropped table
--
@@ -205,49 +204,6 @@ select pg_truncate_visibility_map('test_partition');
(1 row)
--- test the case where vacuum phase I does not need to modify the heap buffer
--- and only needs to set the VM
-create table test_vac_unmodified_heap(a int);
-insert into test_vac_unmodified_heap values (1);
-vacuum (freeze) test_vac_unmodified_heap;
-select pg_visibility_map_summary('test_vac_unmodified_heap');
- pg_visibility_map_summary
----------------------------
- (1,1)
-(1 row)
-
--- the checkpoint cleans the buffer dirtied by freezing the sole tuple
-checkpoint;
--- truncating the VM ensures that the next vacuum will need to set it
-select pg_truncate_visibility_map('test_vac_unmodified_heap');
- pg_truncate_visibility_map
-----------------------------
-
-(1 row)
-
-select pg_visibility_map_summary('test_vac_unmodified_heap');
- pg_visibility_map_summary
----------------------------
- (0,0)
-(1 row)
-
--- though the VM is truncated, the heap page-level visibility hint,
--- PD_ALL_VISIBLE should still be set
-SELECT (flags & x'0004'::int) <> 0
- FROM page_header(get_raw_page('test_vac_unmodified_heap', 0));
- ?column?
-----------
- t
-(1 row)
-
--- vacuum sets the VM
-vacuum test_vac_unmodified_heap;
-select pg_visibility_map_summary('test_vac_unmodified_heap');
- pg_visibility_map_summary
----------------------------
- (1,1)
-(1 row)
-
-- test copy freeze
create table copyfreeze (a int, b char(1500));
-- load all rows via COPY FREEZE and ensure that all pages are set all-visible
diff --git a/contrib/pg_visibility/meson.build b/contrib/pg_visibility/meson.build
index 8a17050f2ac..1d2dd3ee572 100644
--- a/contrib/pg_visibility/meson.build
+++ b/contrib/pg_visibility/meson.build
@@ -34,9 +34,13 @@ tests += {
],
},
'tap': {
+ 'env': {
+ 'enable_injection_points': get_option('injection_points') ? 'yes' : 'no',
+ },
'tests': [
't/001_concurrent_transaction.pl',
't/002_corrupt_vm.pl',
+ 't/003_vacuum_freeze.pl',
],
},
}
diff --git a/contrib/pg_visibility/sql/pg_visibility.sql b/contrib/pg_visibility/sql/pg_visibility.sql
index 57af8a0c5b6..5af06ec5b76 100644
--- a/contrib/pg_visibility/sql/pg_visibility.sql
+++ b/contrib/pg_visibility/sql/pg_visibility.sql
@@ -1,5 +1,4 @@
CREATE EXTENSION pg_visibility;
-CREATE EXTENSION pageinspect;
--
-- recently-dropped table
@@ -95,25 +94,6 @@ select count(*) > 0 from pg_visibility_map_summary('test_partition');
select * from pg_check_frozen('test_partition'); -- hopefully none
select pg_truncate_visibility_map('test_partition');
--- test the case where vacuum phase I does not need to modify the heap buffer
--- and only needs to set the VM
-create table test_vac_unmodified_heap(a int);
-insert into test_vac_unmodified_heap values (1);
-vacuum (freeze) test_vac_unmodified_heap;
-select pg_visibility_map_summary('test_vac_unmodified_heap');
--- the checkpoint cleans the buffer dirtied by freezing the sole tuple
-checkpoint;
--- truncating the VM ensures that the next vacuum will need to set it
-select pg_truncate_visibility_map('test_vac_unmodified_heap');
-select pg_visibility_map_summary('test_vac_unmodified_heap');
--- though the VM is truncated, the heap page-level visibility hint,
--- PD_ALL_VISIBLE should still be set
-SELECT (flags & x'0004'::int) <> 0
- FROM page_header(get_raw_page('test_vac_unmodified_heap', 0));
--- vacuum sets the VM
-vacuum test_vac_unmodified_heap;
-select pg_visibility_map_summary('test_vac_unmodified_heap');
-
-- test copy freeze
create table copyfreeze (a int, b char(1500));
diff --git a/contrib/pg_visibility/t/003_vacuum_freeze.pl b/contrib/pg_visibility/t/003_vacuum_freeze.pl
new file mode 100644
index 00000000000..382539fb9c4
--- /dev/null
+++ b/contrib/pg_visibility/t/003_vacuum_freeze.pl
@@ -0,0 +1,70 @@
+
+# Copyright (c) 2026-2026, PostgreSQL Global Development Group
+
+# Check that vacuum phase I does not need to modify the heap buffer.
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+ plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize the primary node
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+
+# From this point, autovacuum worker will wait at startup.
+$node->safe_psql('postgres',
+ "SELECT injection_points_attach('autovacuum-worker-start', 'wait');");
+
+# Create a sample table and run vacuum
+$node->safe_psql("postgres",
+ "CREATE EXTENSION pg_visibility;\n"
+ . "CREATE EXTENSION pageinspect;\n"
+ . "create table test_vac_unmodified_heap(a int);\n"
+ . "insert into test_vac_unmodified_heap values (1);\n"
+ . "vacuum (freeze) test_vac_unmodified_heap;");
+
+my $result = $node->safe_psql('postgres', qq(select pg_visibility_map_summary('test_vac_unmodified_heap');));
+like($result, qr/(1,1)/, 'pg_visibility_map_summary returned as expected');
+
+
+# truncating the VM ensures that the next vacuum will need to set it
+$node->safe_psql("postgres",
+ "CHECKPOINT;\n"
+ . "select pg_truncate_visibility_map('test_vac_unmodified_heap');\n");
+
+$result = $node->safe_psql('postgres', qq(
+ select pg_visibility_map_summary('test_vac_unmodified_heap');));
+like($result, qr/(0,0)/, 'page_header returned as expected');
+
+
+# though the VM is truncated, the heap page-level visibility hint,
+# PD_ALL_VISIBLE should still be set
+
+$result = $node->safe_psql('postgres', qq(
+ SELECT 'page flags is: '||(flags & x'0004'::int)::text FROM page_header(get_raw_page('test_vac_unmodified_heap', 0));));
+like($result, qr/page flags is: 4/, 'page_header returned as expected');
+
+
+# vacuum sets the VM
+$node->safe_psql("postgres",
+ "vacuum test_vac_unmodified_heap;\n");
+
+$result = $node->safe_psql('postgres', qq(
+ select pg_visibility_map_summary('test_vac_unmodified_heap');));
+like($result, qr/(1,1)/, 'page_header returned as expected');
+
+# Release injection point.
+$node->safe_psql('postgres',
+ "SELECT injection_points_detach('autovacuum-worker-start');");
+
+done_testing();
--
2.43.0
view thread (17+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
In-Reply-To: <CALdSSPjnnXyJ3Ma6VTfYOT2KvOf_f6poMrPtAjNc5V2iFSE8Vw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox