If you have a BRIN index with autosummarize=on, and it's based on a function that requires a snapshot, autovacuum will simply error out when trying to summarize a range. Here's a reproducer:
create table journal (d timestamp); create function packdate(d timestamp) returns text as $$ begin return to_char(d, 'yyyymm'); end; $$ language plpgsql returns null on null input immutable; create index on journal using brin (packdate(d)) with (autosummarize = on, pages_per_range = 1); Now insert some data, insert into journal select generate_series(timestamp '2025-01-01', '2025-12-31', '1 day'); and wait for autovacuum to fire. You'll get an error like 2025-11-03 12:17:42.263 CET [536755] ERROR: cannot execute SQL without an outer snapshot or portal This patch fixes it. I haven't given much thought to adding a good way to test this yet ... -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "Someone said that it is at least an order of magnitude more work to do production software than a prototype. I think he is wrong by at least an order of magnitude." (Brian Kernighan)
>From e0f81d98b08512f540300a0c3061480b62917b57 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <[email protected]> Date: Mon, 3 Nov 2025 12:20:22 +0100 Subject: [PATCH] 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. Author: Álvaro Herrera <[email protected]> Reported-by: Giovanni Fabris <[email protected]> Reported-by: Arthur Nascimento <[email protected]> Backpatch-through: 13 Discussion: https://postgr.es/m/[email protected] --- src/backend/postmaster/autovacuum.c | 2 ++ 1 file changed, 2 insertions(+) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 23cca675f00..2d10ee22377 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2596,7 +2596,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. -- 2.47.3
