uncommitted xmin during vacuum, what to do?

Question

Running vacuum on two specific tables in my postgresql 10 database results in the following error:

VACUUM (FULL|ANALYZE|FREEZE|) table;
ERROR:  uncommitted xmin 359569171 from before xid cutoff 435784199 needs to be frozen

The tables are otherwise functioning, I can query/insert/update them (I can query the whole tables without any error). I can also REINDEX them. However, this error stops the nightly VACUUM ANALYZE on the whole database prematurely. The xmin value value seems to be constant, the cutoff value increases monotonously.

How can I fix this? From the very few instances on the web with a similar error, (e.g. https://stackoverflow.com/questions/50354521/postgres-corruption-error-duplicate-rows-with-the-same-primary-key), it seems there should be some bad rows, but I cannot seem to find them.

Answer 1

A somewhat elegant solution that needs no downtime

I can find the specific rows, and update some insignificant column (comment in my case) in them to its current value:

SELECT id, comment FROM table WHERE xmin=359569171;

and then with the results, do updates for each row.

UPDATE table SET comment=(SELECT comment FROM table WHERE id=<ID>) WHERE id=<ID>;

As there were only three rows, I did not bother to create a formalised script.

Old brute force solution

This is what I could come up with first, and it is not the most elegant way to get rid of the message. I simply dump the table, delete all rows, and then restore it. This is the shell script I created, someone may find it useful.

#!/bin/bash

DB=database_name

for TABLE
do
    SAVEFILE=/tmp/$TABLE.sql.gz
    echo "dumping $TABLE to $SAVEFILE"
    su - postgres -c pg_dump $DB --table=$TABLE --data-only | pigz -1 > $SAVEFILE"
    echo "disabling triggers for $TABLE"
    su - postgres -c "psql  $DB -c 'ALTER TABLE $TABLE DISABLE TRIGGER ALL'"
    echo "deleting the contents of $TABLE"
    su - postgres -c "psql $DB -c 'DELETE FROM $TABLE'"
    echo "restoring contents of $TABLE"
    zcat $SAVEFILE | su - postgres -c "psql $DB"
    echo "re-enabling triggers for $TABLE"
    su - postgres -c "psql $DB -c 'ALTER TABLE $TABLE ENABLE TRIGGER ALL'"
done

The drawback of this approach is that the application must be stopped during this operation to avoid inconsistencies, as triggers must be stopped for the table. This causes about half an hour of downtime in my case.

I tried this approach on a copy of the database and it works, but I am still searching for a better solution that may be done without any downtime.