Re: Unexpected cross-database vacuum impact with hot_standby_feedback=on

[Owen Stephens]

On Fri, May 19, 2023 at 9:50 PM Laurenz Albe 
wrote:

> Yes, that's what I would expect.  There is only one "backend_xmin" in
> "pg_stat_replication", which corresponds to the snapshot held by the oldest
> query in any database on the standby server.
>

Thanks for the pointer to pg_stat_replication.

In my situation there aren't any queries against the standby server, both
queries are running against the primary, but backend_xmin does contain the
open
transaction's txid_current() value. Does the standby somehow report the
txid of
the open transaction on the primary back to the primary as if it were
running on
the standby?

Is it an unavoidable limitation of the standby feedback mechanism that xmin
is
not tracked by database? It was certainly a surprise to me to find that
hot_standby_feedback can trigger cross-database dependencies like this.

Thanks,
Owen.

Unexpected cross-database vacuum impact with hot_standby_feedback=on

[Owen Stephens]

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.