Feedback-ID: ie3de48e3:Fastmail
Date: Mon, 3 Nov 2025 12:21:50 +0100
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: BRIN autosummarization lacking a snapshot
Message-ID: <202511031106.h4fwyuyui6fz@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="y3erep2biy2c2i25"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/202511031106.h4fwyuyui6fz%40alvherre.pgsql>
Precedence: bulk


--y3erep2biy2c2i25
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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)

--y3erep2biy2c2i25
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
	filename="0001-Brin-autosummarization-may-need-a-snapshot.patch"
Content-Transfer-Encoding: 8bit

From e0f81d98b08512f540300a0c3061480b62917b57 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
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 <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 ++
 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


--y3erep2biy2c2i25--