"uncommitted xmin 1848639870 from before xid cutoff 1869445924 needs to be frozen" error when attempting to vacuum a table in Postgres

Products

• VMware Tanzu Greenplum

Issue/Introduction

When attempting to vacuum a table, you hit the following error:

postgres=# vacuum freeze analyze test;
ERROR: uncommitted xmin 1848639870 from before xid cutoff 1919495025 needs to be frozen

This is because the xid that is stored in the xmin column is older than the currently committed transactions. If you check the table, you'll see the row is valid. 

postgres=# select xmin,xmax,* from test where xmin = 1848639870;
  xmin  | xmax |    a    | event_dt |    sys_create_ts
------------+------+-------------------+-------------------+--------------------
--------
 1848639870 |  0 | 12345 | 2021-12-17    | 2021-12-17 18:20:23
.233365

This situation usually only occurs when there's been some sort of corruption in the data or the table and it is best to address it as soon as you can.

You can also hit this issue when the postgres autovacuum daemon reports the "uncommitted xmin 1848639870 from before xid cutoff 1869445924 needs to be frozen" error in the Postgres log.

Environment

Product Version: 11.9

Resolution

There are two options to work around this issue: 

Option 1. Restore the table or database from backup.

Option 2. If only a few tables are affected, you can update every row in the table - this resets the xmin value for the row, which will allow you to vacuum it again.

For example:

update test set a = a;