oldest xmin is far in the past :: BUT xmin is not available in system

Hi Team,

It seems vacuum is behaving somewhat weird on postgres database , observing
below HINTS on the vacuum logs

WARNING: oldest xmin is far in the past

HINT: Close open transactions soon to avoid wraparound problems.

You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.

Below is the auto-vacuum status on the bloated tables:

=> SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum

FROM pg_stat_all_tables

ORDER BY n_dead_tup

/ (n_live_tup

* current_setting('autovacuum_vacuum_scale_factor')::float8

+ current_setting('autovacuum_vacuum_threshold')::float8)

DESC

LIMIT 10;

schemaname | relname | n_live_tup | n_dead_tup |
last_autovacuum

------------+---------------------+------------+------------+-------------------------------

pg_catalog | pg_statistic | 136 | 37563 | 2022-04-18
04:00:21.045089+00

public | test1 | 209405206 | 126752908 | 2022-04-18
03:59:43.013758+00

public | test2 | 513770985 | 49258312 | 2022-04-18
04:00:23.24043+00

public | test3 | 90853150 | 4090146 | 2022-04-18
04:00:25.868147+00

pg_catalog | pg_shdepend | 153 | 29 | 2022-04-08
12:16:02.816631+00

pg_catalog | pg_index | 73 | 18 |

pg_toast | pg_toast_2619 | 16 | 12 | 2022-03-13
23:01:54.334003+00

pg_catalog | pg_class | 425 | 19 | 2022-03-01
13:15:57.534378+00

pg_catalog | pg_proc | 2457 | 48 |

pg_toast | pg_toast_2618 | 252 | 10 |

i tried to vacuum the the first table pg_statistic , Below is the log

postgres=> VACUUM (VERBOSE) pg_statistic;

WARNING: oldest xmin is far in the past

HINT: Close open transactions soon to avoid wraparound problems.

You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.

INFO: aggressively vacuuming "pg_catalog.pg_statistic"

INFO: "pg_statistic": found 0 removable, 37699 nonremovable row versions
in 6331 out of 6351 pages

DETAIL: 37563 dead row versions cannot be removed yet, oldest xmin:
648320155

There were 3340 unused item identifiers.

Skipped 0 pages due to buffer pins, 20 frozen pages.

0 pages are entirely empty.

CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.

WARNING: oldest xmin is far in the past

HINT: Close open transactions soon to avoid wraparound problems.

You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.

INFO: aggressively vacuuming "pg_toast.pg_toast_2619"

INFO: "pg_toast_2619": found 0 removable, 16 nonremovable row versions in
3 out of 11 pages

DETAIL: 12 dead row versions cannot be removed yet, oldest xmin: 648320155

There were 11 unused item identifiers.

Skipped 0 pages due to buffer pins, 8 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

VACUUM

Table is getting vacuumed but not able to remove the dead tuples
because of *oldest
xmin: 648320155* , but the mentioned xim is not associated with long
running quries or stale replication slots or prepared transactions.

*Long running:*

postgres=> SELECT now()-query_start,pid, datname, usename, state,
backend_xmin

FROM pg_stat_activity

WHERE backend_xmin IS NOT NULL

ORDER BY age(backend_xmin) DESC;

?column? | pid | datname | usename | state |
backend_xmin

-----------------+-------+-------------------+---------+--------+--------------

00:00:29.910155 | 539 | postgres | | active | 832858371

00:00:23.766305 | 1211 | postgres | | active | 832858509

00:00:00.756961 | 2151 | postgres | | active | 832859484

00:00:00.060784 | 30833 | postgres | root | active | 832859508

00:00:00.004473 | 29270 | postgres | root | active | 832859508

00:00:00.009809 | 29271 | postgres | root | active | 832859508

00:00:00.015169 | 27145 | postgres | root | active | 832859508

00:00:00 | 1450 | postgres | postgres | active | 832859508

00:00:00.010672 | 544 | postgres | root | active | 832859508

00:00:00.034516 | 19940 | postgres | root | active | 832859508

(10 rows)

*stale replication slots:*

postgres=> SELECT slot_name, slot_type, database, xmin

FROM pg_replication_slots

ORDER BY age(xmin) DESC;

slot_name | slot_type | database | xmin

-----------+-----------+----------+------

(0 rows)

*Prepared transaction's :*

postgres=> SELECT gid, prepared, owner, database, transaction AS xmin

postgres-> FROM pg_prepared_xacts

postgres-> ORDER BY age(transaction) DESC;

gid | prepared | owner | database | xmin

-----+----------+-------+----------+------

(0 rows)

Checked for long running queries on replica side , but haven't found any

postgres=> show hot_standby_feedback ;

hot_standby_feedback

----------------------

on

(1 row)

postgres=> SELECT pid, age(current_timestamp, xact_start),usename
,state,left(query,100)

FROM pg_stat_activity

WHERE state <> 'idle' and pid<>pg_backend_pid();

;

pid | age | usename | state | left

-----+-----+---------+-------+------

(0 rows)

postgres=> select pg_is_in_recovery();

pg_is_in_recovery

-------------------

t

(1 row)

Regards,

BK