PostgreSQL 9.3 – Error: Failed to Re-find Parent Key in Index

indexpostgresqlpostgresql-9.3

I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently the VM ran out of disk space (resulting in no space left on device errors showing up in my logs).

I cleared the space on the device and everything came back online.

But now, for a minority of my requests, I've started receiving a django.db.utils:DatabaseError alert, with the description failed to re-find parent key in index "links_grouptraffic_time" for split pages 24582/24583. links_grouptraffic is one of my data models, and time is an attribute within that data model.

Can someone explain the background of what this means, and a remedy for this problem? Thanks in advance!

Best Answer

Your database is likely corrupted, especially the indexes. You can rebuild all indexes and check all tables on a database by:

REINDEX SYSTEM; -- run once
REINDEX DATABASE <your dbname>; -- run for each database
VACUUM (FULL VERBOSE ANALYZE); -- also run for each database after reindexing

Demo:

Setup:

CREATE ROLE thegroup;
CREATE USER user1 IN ROLE thegroup;
CREATE USER user2 IN ROLE thegroup;
CREATE TABLE t1 ( x integer not null );
ALTER TABLE t1 OWNER TO user1;

Without further changes, here's what happens if user2 tries to add an index on t1.x:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
ERROR:  must be owner of relation t1

The solution is to grant the table ownership to the shared role. As user1 or a superuser:

ALTER TABLE t1 OWNER TO thegroup;

now:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
CREATE INDEX
regress=>

PostgreSQL Error – Failed to Find Parent Tuple

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.

Best Answer

Related Solutions

Postgresql – Error creating index on PostgreSQL 9.1

Demo:

PostgreSQL Error – Failed to Find Parent Tuple

Related Question