From a95e3d84c0e0ffd1e27c185dd69d053e43f2f8b5 Mon Sep 17 00:00:00 2001
From: =?utf8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Tue, 4 Nov 2025 13:23:26 +0100
Subject: [PATCH] BRIN autosummarization may need a snapshot
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

It's possible to define BRIN indexes on functions that require a
snapshot to run, but the autosummarization feature introduced by commit
7526e10224f0 fails to provide one.  This causes autovacuum to leave a
BRIN placeholder tuple behind after a failed work-item execution, making
such indexes less efficient.  Repair by obtaining a snapshot prior to
running the task, and add a test to verify this behavior.

Author: Álvaro Herrera <alvherre@kurilemu.de>
Reported-by: Giovanni Fabris <giovanni.fabris@icon.it>
Reported-by: Arthur Nascimento <tureba@gmail.com>
Backpatch-through: 13
Discussion: https://postgr.es/m/202511031106.h4fwyuyui6fz@alvherre.pgsql
---
 src/backend/postmaster/autovacuum.c     |  2 ++
 src/test/modules/brin/t/01_workitems.pl | 46 ++++++++++++++++++++++---
 2 files changed, 44 insertions(+), 4 deletions(-)

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 5084af7dfb6..59ec45a4e96 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2556,7 +2556,9 @@ deleted:
 		workitem->avw_active = true;
 		LWLockRelease(AutovacuumLock);
 
+		PushActiveSnapshot(GetTransactionSnapshot());
 		perform_work_item(workitem);
+		PopActiveSnapshot();
 
 		/*
 		 * Check for config changes before acquiring lock for further jobs.
diff --git a/src/test/modules/brin/t/01_workitems.pl b/src/test/modules/brin/t/01_workitems.pl
index c3b1fb51706..0744b8825ef 100644
--- a/src/test/modules/brin/t/01_workitems.pl
+++ b/src/test/modules/brin/t/01_workitems.pl
@@ -24,23 +24,61 @@ $node->safe_psql(
 	 create index brin_wi_idx on brin_wi using brin (a) with (pages_per_range=1, autosummarize=on);
 	 '
 );
+# Another table with an index that requires a snapshot to run
+$node->safe_psql(
+	'postgres',
+	'create table journal (d timestamp) with (fillfactor = 10);
+	 create function packdate(d timestamp) returns text language plpgsql
+	   as $$ begin return to_char(d, \'yyyymm\'); end; $$
+	   returns null on null input immutable;
+	 create index brin_packdate_idx on journal using brin (packdate(d))
+	   with (autosummarize = on, pages_per_range = 1);
+	 '
+);
+
 my $count = $node->safe_psql('postgres',
 	"select count(*) from brin_page_items(get_raw_page('brin_wi_idx', 2), 'brin_wi_idx'::regclass)"
 );
-is($count, '1', "initial index state is correct");
+is($count, '1', "initial brin_wi_index index state is correct");
+$count = $node->safe_psql('postgres',
+	"select count(*) from brin_page_items(get_raw_page('brin_packdate_idx', 2), 'brin_packdate_idx'::regclass)"
+);
+is($count, '1', "initial brin_packdate_idx index state is correct");
 
 $node->safe_psql('postgres',
 	'insert into brin_wi select * from generate_series(1, 100)');
+$node->safe_psql('postgres',
+	"insert into journal select * from generate_series(timestamp '1976-08-01', '1976-10-28', '1 day')"
+);
+
+# Give a little time for autovacuum to react.  This matches the naptime
+# configured above.
+sleep(1);
 
 $node->poll_query_until(
 	'postgres',
 	"select count(*) > 1 from brin_page_items(get_raw_page('brin_wi_idx', 2), 'brin_wi_idx'::regclass)",
 	't');
 
-$count = $node->safe_psql('postgres',
-	"select count(*) > 1 from brin_page_items(get_raw_page('brin_wi_idx', 2), 'brin_wi_idx'::regclass)"
+$count = $node->safe_psql(
+	'postgres',
+	"select count(*) from brin_page_items(get_raw_page('brin_wi_idx', 2), 'brin_wi_idx'::regclass)
+	 where not placeholder;"
+);
+cmp_ok($count, '>', '1', "$count brin_wi_idx ranges got summarized");
+
+$node->poll_query_until(
+	'postgres',
+	"select count(*) > 1 from brin_page_items(get_raw_page('brin_packdate_idx', 2), 'brin_packdate_idx'::regclass)",
+	't');
+
+$count = $node->safe_psql(
+	'postgres',
+	"select count(*) from brin_page_items(get_raw_page('brin_packdate_idx', 2), 'brin_packdate_idx'::regclass)
+	 where not placeholder;"
 );
-is($count, 't', "index got summarized");
+cmp_ok($count, '>', '1', "$count brin_packdate_idx ranges got summarized");
+
 $node->stop;
 
 done_testing();
-- 
2.47.3