3

I have a table which has just started giving me the following error when trying to set a primary key, create an index or take a backup:

ERROR: MultiXactId 1092514672 has not been created yet -- apparent wraparound
SQL state: XX000

After a bit of searching online, I've not found a clear solution to the issue. There is some indication it might be a bug in older versions of Postgres.

I tried taking a backup of the table using pg_dump, as one option might be to just restore a fresh version, but got the same error.

Another possible solution I came across was to run select * from table for update;, but this also does not work with the same error given.

Running Postgres 9.4.3 on Debian 8.1. (Postgres has not been updated recently, thought I'd mention this as for some people this seems to cause the appearance of the error.)

Has anyone come across this before? Any ideas for a solution?

Update: As suggested in the answer below from Erwin, I tried VACUUM FREEZE tbl;, but this also returned the same error.

Update 2: I have updated to Postgres version 9.4.8. This has had no effect, the error remains.

  • 3
    There were some bugs fixed regarding this issue starting with 9.4.4 - you should really update to 9.4.8 – a_horse_with_no_name Jul 11 '16 at 20:39
3

I would advise to backup first ... but that's a catch 22, since you can't.

Either way, since the error revolves around a fictional xid wraparound I thought this might solve your immediate problem:

VACUUM FREEZE tbl;

The chapter Preventing Transaction ID Wraparound Failures in the manual has details.

But turns out, this runs into the same problem.

Of course, you can delete and recreate the table if you have the data outside Postgres.

The only remaining option I see is to upgrade to the latest point release and hope your problem is covered in the various patches that have been added, like @a_horse commented.

However, after some more research I found that the core hackers have been working on a solution for just this problem in the past weeks.
Details in this thread on pgsql-hackers.
The commit message.

So I would hold back and upgrade straight to Postgres 9.4.9, which should be out soon:
The next point release is planned for August 11th, 2016 at the latest.

  • Many thanks for your answer @Erwin, but after a couple of hours running VACUUM FREEZE, it came up with the same error. Do you think I have any other options, or am I going to have to drop this table and reload from an earlier backup? – Matt Jul 12 '16 at 10:09
  • @Matt: I did some more research, see above. Additionally, you might want to post your case on pgsql-general or pgsql-admin and ask whether this is covered in the patch being developed. postgresql.org/list – Erwin Brandstetter Jul 12 '16 at 13:42
  • Thanks for your additional help @Erwin. I'll update to 9.4.8 anyway I think, just to see if that makes a difference, and the 9.4.9 as soon as it's released. I'll come back and update my question and accept your answer when it works! – Matt Jul 12 '16 at 15:10
  • @Matt: You're right, updating to the latest point release is almost always a good idea. And I would be interested if either solves your problem. – Erwin Brandstetter Jul 12 '16 at 16:00
  • @Matt did this solve your problem? Can we get a status update? Would you be willing to self-answer or mark this as chosen if it guided you in the right direction? – Evan Carroll Jan 11 '17 at 18:26

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.