What does "ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"" mean?

Question

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?

Answer 1

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.