On 2025-Nov-04, Michael Paquier wrote: > Spawning an autovacuum worker can feel artistic as we try to make the > tests run fast, but it's not that bad. The trick is to use an > "autovacuum_naptime = 1". Then you could either scan the server logs > for some 'autovacuum: processing database "blah"', or just a polling > query based on pg_stat_all_tables.autovacuum_count. See for example > 006_signal_autovacuum.pl.
Ah yes ... and, actually, we already have a file doing a closely related thing, so I added to it. Here's the patch for master. Backbranches are essentially identical, modulo these changes for 13 and 14: -use Test::More tests => 2; +use Test::More tests => 4; I'm glad we got rid of that :-) With my initial try of this test, just counting the number of BRIN tuples, I was _really_ surprised that the index did indeed contain the expected number of tuples, even when the error was being thrown. This turned out to be expected, because the way BRIN summarization works is that we insert a placeholder tuple first, then update it to the correct value, and the error only aborts the second part. That's why I needed to add a WHERE clause to only count non-placeholder tuples. I also added a 'sleep(1)', to avoid looping on the query when we know autovacuum can't possibly have had a chance to run yet. I unleashed CI on branches 15 and master, and will push soon if they both turn green. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "La virtud es el justo medio entre dos defectos" (Aristóteles)
>From fc8067aa4d905edf1f53a097c87b1e0883dd7c8c Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <[email protected]> Date: Tue, 4 Nov 2025 12:38:24 +0100 Subject: [PATCH v2] BRIN autosummarization may need a snapshot MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 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. Repair, and add a test to verify that it works. Author: Álvaro Herrera <[email protected]> Backpatch-through: 13 --- 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;" ); -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(); -- 2.47.3
