Hi,
We are seeing that vacuum is prevented from cleaning dead tuples by an open
transaction in a different database (where both connections are made against the
primary server) when hot_standby_feedback = on but not when it is off. Is this
cross-database interaction an expected effect of enabling hot_standby_feedback,
even if the connections interact only with the primary not the replica? I
haven't managed to find anything in the documentation describing this effect if
so.
To reproduce, consider a PG 14.7 setup with a primary server that has a replica
with hot_standby_feedback enabled. Create two databases, with a table containing
some rows in each. Then, open a `psql` session against each database.
In one, open a transaction, and in the other, delete the rows from the table and
attempt to vacuum:
```
second_example_db=# BEGIN;
BEGIN
second_example_db=*# SELECT txid_current();
txid_current
--------------
770
(1 row)
second_example_db=*#
```
```
first_example_db=# DELETE FROM first_table;
DELETE 2
first_example_db=# VACUUM VERBOSE first_table;
INFO: vacuuming "public.first_table"
INFO: table "first_table": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL: 2 dead row versions cannot be removed yet, oldest xmin: 770
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
VACUUM
first_example_db=#
```
Notice that the oldest xmin is reported as that of the transaction in a
different database. If I COMMIT/ROLLBACK the transaction in `second_example_db`,
then after a short while, the same VACUUM command succeeds:
```
...
INFO: table "first_table": found 2 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 772
...
```
If I recreate the hot-standby replica but with hot_standby_feedback = off, then
under the same reproduction, vacuum is able to remove the dead rows despite the
open transaction in a different database, as expected.
Is anyone able to shed any light on this behaviour and whether or not it is
intentional?
Thanks,
Owen.