| 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 |
| 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 |