Horton

BRIN autosummarization lacking a snapshot

Álvaro Herrera <alvherre@kurilemu.de>

View thread
From: Álvaro Herrera <alvherre@kurilemu.de>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-11-03T11:21:50Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix snapshot handling bug in recent BRIN fix

  2. BRIN autosummarization may need a snapshot

Attachments

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)