Fixing Corrupted Rows in PostgreSQL: A Real-World Example

on

In this blog post, I document a real issue I encountered when trying to delete specific rows in a PostgreSQL table. The errors were unusual and pointed to data corruption. Here's how I diagnosed and resolved it step by step.

🧨 The Problem

I attempted to delete a row using this query:

DELETE FROM job_jobarchive WHERE job_id = 43788508;

But it returned an unexpected error:

ERROR: invalid memory alloc request size 1953391981

Then I checked the row directly:

SELECT * FROM job_jobarchive WHERE job_id = 43788508;

Same error again. To isolate the row, I used:

SELECT ctid FROM job_jobarchive WHERE job_id = 43788508;

This returned:

(28005,1)

Then I tried to delete it using the low-level physical reference:

DELETE FROM job_jobarchive WHERE ctid = '(28005,1)';

But even that failed with the same memory allocation error.

🧱 A Second Error: Corrupted TOAST Data

Another job_id gave a different error when deleting:

DELETE FROM job_jobarchive WHERE job_id = 74820786;
ERROR: compressed data is corrupted

This indicated TOAST table corruption.

🔧 The Workaround: Recreate a Clean Table

Since direct deletion was not possible, I decided to create a clean copy of the table excluding the two problematic rows.

Step 1: Create a clean copy of the table structure

CREATE TABLE job_jobarchive_clean (LIKE job_jobarchive INCLUDING ALL);

Step 2: Insert all valid rows, excluding the corrupted ones

INSERT INTO job_jobarchive_clean
SELECT * FROM job_jobarchive
WHERE job_id NOT IN (43788508, 74820786);

Step 3: Verify row counts

SELECT COUNT(*) FROM job_jobarchive;
SELECT COUNT(*) FROM job_jobarchive_clean;

Output:

job_jobarchive:       3,277,907 rows
job_jobarchive_clean: 3,277,905 rows

Step 4: Swap tables

ALTER TABLE job_jobarchive RENAME TO job_jobarchive_broken;
ALTER TABLE job_jobarchive_clean RENAME TO job_jobarchive;

🗑️ Dropping the Corrupted Table

I tried to drop the old table:

DROP TABLE job_jobarchive_broken;

But got:

ERROR: cannot drop table job_jobarchive_broken because other objects depend on it

The fix was to either drop it with CASCADE or remove dependencies manually:

Option: Drop with CASCADE (⚠️ dangerous)

DROP TABLE job_jobarchive_broken CASCADE;

Make sure to understand what will be dropped along with it!

✅ Final Thoughts

This process avoided touching corrupted rows directly and helped recover the usable data. It's a good reminder to:

  • Regularly back up your data
  • Use tools like pg_dump or pg_surgery (PostgreSQL 13+)
  • Watch for early signs of corruption

Hope this helps other developers facing similar PostgreSQL corruption issues!

Comments

Post a Comment