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
vacuum_tables_options.patch
Description: Binary data
