BRIN autosummarization lacking a snapshot

From: Álvaro Herrera <alvherre(at)kurilemu(dot)de>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: BRIN autosummarization lacking a snapshot
Date: 2025-11-03 11:21:50
Message-ID: 202511031106.h4fwyuyui6fz@alvherre.pgsql
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Attachment Content-Type Size
0001-Brin-autosummarization-may-need-a-snapshot.patch text/x-diff 1.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ranier Vilela 2025-11-03 11:36:18 Re: Avoid overflow (src/backend/utils/adt/formatting.c)
Previous Message Dean Rasheed 2025-11-03 11:21:49 Re: Docs and tests for RLS policies applied by command type