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

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.

  • 1
    Do you have any very long running transactions, or any forgotten about prepared transactions?
    – jjanes
    Sep 2, 2019 at 14:37
  • There are frequently long running transactions in the application, but there are no current prepared transactions (SELECT * FROM pg_prepared_xacts ; returns nothing).
    – P.Péter
    Sep 3, 2019 at 7:10
  • PostgreSQL really thinks that there is an ongoing unfinished transaction blocked since version 359569171. You should try to find that one... SELECT * FROM pg_stat_activity WHERE backend_xmin = 359569171 ? Sep 10, 2019 at 16:27
  • @PatrickMevzek unfortunately, that SQL query returns zero rows.
    – P.Péter
    Sep 12, 2019 at 8:48

1 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.



    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'"

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.

  • You got the right idea. I would trucate rather than delete from the table. As well as disabling triggers, you could drop indexes and recreate them afterwards, that should make it faster. I would probably copy the data out and in using \copy rather than pg_dump, that way I could wrap all of this into a single sql file and run it as a single transaction.
    – jjanes
    Sep 3, 2019 at 11:55
  • If this solves your problem then it seems you are exactly in this case: lerner.co.il/2015/09/17/… Sep 10, 2019 at 19:19
  • @jjanes I'm running into this issue, but haven't the knowledge of this. Would you suggest raising vacuum_freeze_min_age temporarily, to circumvent the "cutoff"? And based on other articles I've read, this seems to be related to corruption--would you agree?
    – richyen
    Dec 11, 2019 at 1:20
  • Updating the whole table also worked for me in the case there were many bad rows: UPDATE table SET comment = comment;. Apr 22, 2020 at 5:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.