Silent data loss due to two vacuum running logically different tables using the same relfilenode.

[MasahikoSawada]

Hi,

I've observed a silent data loss problem if (auto)vacuum, swapping tables by pg_repack, and INSERT are executed concurrently. The root cause is that while pg_repack is swapping the target table and its temp table, these two tables have different OID but use the same underlying file (i.e., use the same relfilenode), and two vacuum processes can concurrently process the same underlying file. I've confirmed this problem with PostgreSQL 15.4 and pg_repack 1.4.8. I think it impacts other PostgreSQL versions and pg_repack versions as well.

The reproducible step is:

Preparation

create table test (id int primary key, a int);
insert into test values (1, 1), (2,2), (3,3);

These tuples are inserted at TID (0, 1), (0, 2), and (0, 3) on the `test table, respectively.

Steps

1. Run pg_repack with debugger for the table A, and stop pg_repack after copying table data to its temp table by creating a breakpoint, for example at  pg_repack.c:1537.
2. Update a tuple of test table A, say UPDATE test SET id = 999, a = 999 WHERE id = 1. This change is logged to its log table.
3. Let pg_repack resume, its applies the log. And stop pg_repack after all logs are applied, for example at pg_repack.c:L1598.
       -  In the temp table, the tuple at (0, 1) becomes garbage now.
       - Note that conn2 already holds an AccessExclusiveLock on the test table A, but nobody has a lock on the temp table.
4. Run vacuum on the temp table and stop it before calling to lazy_vacuum_heap().
       - vacuum scans the temp table and collects TID (0,1) as dead tuple, but does not actually remove the itemid there yet.
5. Let pg_repack resume, it swaps the test table and the temp table. And stop pg_repack right after swapping them, for example at pg_repack.c:1617.
       - Now both the table test and the temp table use the same relfilenode. And conn2 already released AccessExclusiveLock on the test table.
6. run another vacuum on the test table, and it removes a tuple at TID (0,1) (since the first vacuum process doesn't remove it yet).
7. Insert a new tuple into the test table, say INSERT INTO test VALUES (4,4).
       - This new tuple is inserted at TID (0,1).
8. Let the first vacuum resume, it ends up removing the tuple at TID (0,1) we inserted at step 7.

In the above producer I used vacuum command, but what I actually observed was that autovacuum processes run on the test table and its temp table because they got older than autovacuum_freeze_max_age.

A simple solution would be to acquire AccessExclusiveLock on both the target table and its temp table before swapping them.

[MasahikoSawada]

It's resolved by commit f5aa388.