Fixes inconsistent behavior in vacuum when it processes multiple relations

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

Responses

Browse pgsql-hackers by date

  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