oldest xmin is far in the past :: BUT xmin is not available in system - Mailing list pgsql-general

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