postgres.git / summary / log / commit / refs
commit 419ffde235469bd7459bd52b7eeed76a7520853a
Author: Álvaro Herrera <alvherre@kurilemu.de>
Date: Tue Nov 04 12:23:26 2025 +0000
First released in: 13.23, 14.20, 15.15, 16.11, 17.7, 18.1
BRIN autosummarization may need a snapshot
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 | 37 ++++++++++++++++++++++++++++++---
2 files changed, 36 insertions(+), 3 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 451fb90a610..80d71eb1039 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2542,7 +2542,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..ccc3f420897 100644
--- a/src/test/modules/brin/t/01_workitems.pl
+++ b/src/test/modules/brin/t/01_workitems.pl
@@ -24,13 +24,31 @@ $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')");
$node->poll_query_until(
'postgres',
@@ -38,9 +56,22 @@ $node->poll_query_until(
't');
$count = $node->safe_psql('postgres',
- "select count(*) > 1 from brin_page_items(get_raw_page('brin_wi_idx', 2), 'brin_wi_idx'::regclass)"
+ "select count(*) from brin_page_items(get_raw_page('brin_wi_idx', 2), 'brin_wi_idx'::regclass)
+ where not placeholder;"
);
-is($count, 't', "index got summarized");
+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;"
+);
+cmp_ok($count, '>', '1', "$count brin_packdate_idx ranges got summarized");
+
$node->stop;
done_testing();
[parent: 1baae827ef51]