2

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.

  • 1
    Do you have any very long running transactions, or any forgotten about prepared transactions? – jjanes Sep 2 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 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 ? – Patrick Mevzek Sep 10 at 16:27
  • @PatrickMevzek unfortunately, that SQL query returns zero rows. – P.Péter Sep 12 at 8:48
0

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.

  • 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 at 11:55
  • If this solves your problem then it seems you are exactly in this case: lerner.co.il/2015/09/17/… – Patrick Mevzek Sep 10 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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