Re: invalid memory alloc request size 1765277700 Error Question - Mailing list pgsql-general

Tom, Scott,
Thank you very much for your advice and right questions that lead to me the solution - In summary, I was able to identify and delete all corrupted data with no data loss. Everything add up once I disabled index per Tom's advice.

Here is the detail report:
Review the data again in order to answer Scott's questions. Both shows that it use index:

EXPLAIN SELECT * FROM table ORDER BY table_key OFFSET 526199 LIMIT 1;
"Limit  (cost=42574.62..42574.70 rows=1 width=220)"
"  ->  Index Scan using table_pkey on table  (cost=0.00..118098.91 rows=1459638 width=220)"

EXPLAIN SELECT * FROM table WHERE table_key = 304458; 
"Index Scan using table_pkey on table  (cost=0.00..7.38 rows=1 width=220)"
"  Index Cond: (table_key = 304458)"

The columns I was able to review were up to 5th columns, including anything after that would shows data corruptions. Column types are varied (varchar, boolean, text, integer)

As soon as disabled Index per Tom's advice, everything became clear and make sense.
SELECT table_key FROM table GROUP BY table_key HAVING count(table_key) > 1
Returned 5 rows. Before disabling index, it returned 0 row.

The I performed the following query to identify exactly what record to delete:
SELECT * FROM table WHERE table_key=304458 -- error
SELECT ctid FROM table WHERE table_key=304458 -- (2021,22) (17958,10)
SELECT * FROM table WHERE ctid='(2021,22)' -- GOOD
SELECT * FROM table WHERE ctid='(17958,10)' -- BAD  ERROR:  invalid memory alloc request size 1765277700

Removed all bad records and I was finally able to REINDEX & pg_dump! Now everything looks VERY HAPPY.

One thing I failed to report/notice earlier is that there were 2 type of errors:
Some rows returned ERROR:  invalid memory alloc request size 1765277700
Some rows returned ERROR:  compressed data is corrupt

Thank you so much again for all your help.

- Naoko




--
Naoko Reeves