PostgreSQL Error – Failed to Find Parent Tuple

postgresqlpostgresql-9.3

After being bitten by the bug mentioned in the release notes of 9.3.4, I have realized that a very small part of my data is corrupt, after doing a failover. The bug showed it self as duplicate rows, with the same primary key.
Now I can easily afford to delete some data from my database if that’s what it takes to fix my problem, so I just deleted those duplicates whole hug. After doing this, I decided to write a script that drops/recreates all constraints (Pkeys and Fkeys), in order to validate that no more of my data is corrupt. This worked well, until I bumped into the following error, while creating one of the foreign keys:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

What is it, and how do I solve it?

Best Answer

This error message is new in postgres and is indeed related to the replication bug fixed in 9.3.3

As mentioned in the git commit from Mon, 17 Mar 2014:

The recently-fixed bug in WAL replay could result in not finding a parent tuple for a heap-only tuple. The existing code would either Assert or generate an invalid index entry, neither of which is desirable. Throw a regular error instead.

Since the parent tuple is an internal notion of postgres, the implication would be that the data structure for this relation is in a corrupted state, beyond troubleshooting at the SQL level.

The safest way out of this mess would be, if at all possible, to pg_dump the affected databases, then drop and reload them, and then recreate the constraints on this clean state.

Related Question