Postgresql – Postgres – MultiXactId error

postgresqlpostgresql-9.4

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.

Best Answer

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.

Related Question