Обсуждение: Unexpected cross-database vacuum impact with hot_standby_feedback=on

Поиск
Список
Период
Сортировка

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.


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

От
Laurenz Albe
Дата:
On Thu, 2023-05-18 at 17:34 +0100, Owen Stephens wrote:
> 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?

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.

Yours,
Laurenz Albe

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

От
Owen Stephens
Дата:
On Fri, May 19, 2023 at 9:50 PM Laurenz Albe <laurenz.albe@cybertec.at> 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.