From: | shihao zhong <zhong950419(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Fixes inconsistent behavior in vacuum when it processes multiple relations |
Date: | 2025-06-18 15:15:31 |
Message-ID: | CAGRkXqTo+aK=GTy5pSc-9cy8H2F2TJvcrZ-zXEiNJj93np1UUw@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi team,
One of our customers recently encountered an issue with PostgreSQL's
pg_cron and VACUUM ANALYZE. They had configured a table with
vacuum_truncate=false to prevent exclusive lock contention, assuming
this would apply globally. However, VACUUM ANALYZE executed across the
entire database doesn't honor this table-specific setting, though
autovacuum does.
I investigated the code and found a small bug with how we're passing
the VacuumParams pointer.
The call flow is
ExecVacuum -> vacuum -> vacuum_rel
The initial VaccumParams pointer is set in ExecVacuum
In vacuum_rel, this pointer might change because it needs to determine
whether to truncate and perform index_cleanup.
Vacuuming a single table works as expected because the options are
applied at the table level. However, when vacuuming multiple tables,
the second table reuses the modified parameters set by the first
table's vacuum_rel.
We can easy repro that with following SQL and same with index_cleanup:
create table test(id int);
create table test_1(id int) with (vacuum_truncate=false);
insert into test select generate_series(1,1000);
insert into test_1 select generate_series(1,1000);
delete from test;
delete from test_1;
vacuum (analyze) test_1, test;
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method |
Size | Description
--------+--------+-------+----------+-------------+---------------+-------+-------------
public | test | table | postgres | permanent | heap | 72 kB |
public | test_1 | table | postgres | permanent | heap | 72 kB |
(2 rows)
I've implemented a fix and included a regression test in the patch.
Thanks,
Shihao
Attachment | Content-Type | Size |
---|---|---|
vacuum_tables_options.patch | application/octet-stream | 10.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2025-06-18 15:29:16 | Re: pg_dump --with-* options |
Previous Message | Álvaro Herrera | 2025-06-18 15:05:26 | Re: pg_dump misses comments on NOT NULL constraints |