Обсуждение: invalid memory alloc request size 1765277700 Error Question
Version: "PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370), 32-bit"
There was an hardware crash.
after that pg_dump failed with an error:
ERROR: invalid memory alloc request size 1765277700
I searched archive and it indicates that this is data corruption.
I have narrowed down to 7 records or so might be corrupted.
However, something doesn't add up... Please see below:
-- I have narrowed down the row
SELECT * FROM table ORDER BY table_id OFFSET 526199 LIMIT 1 -- ERROR: invalid memory alloc request size 1765277700
-- I was able to view a few columns
SELECT table_id, table_column1, table_column2 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 -- returns one row table_id = 12345
-- using that id to SELECT ALL. It shows fine...I was assuming this will give me same error...
SELECT * FROM table WHERE table_id=12345 -- shows perfectly
-- This also returns value just fine
SELECT table_column3 FROM table WHERE table_id = 12345
-- However this returns an error
SELECT table_column3 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 --error ERROR: invalid memory alloc request size 1765277700
To me it is as if it is accessing to different record.. I thought possibly primary key index might be corrupted.. so attempted
REINDEX TABLE table;
--ERROR: could not create unique index "table_pkey"
--DETAIL: Table contains duplicated values.
-- Now this returns 0 row... Not sure what I am doing wrong here...
SELECT table_id FROM table GROUP BY table_id HAVING count(table_id) > 1
If anyone could advice me why I am able to view record with primary key query but not with OFFSET?
Do I consider these record as corrupted?
If so deleting these records might resolve the error I am getting?
Thank you very much for your time.
--
Naoko
There was an hardware crash.
after that pg_dump failed with an error:
ERROR: invalid memory alloc request size 1765277700
I searched archive and it indicates that this is data corruption.
I have narrowed down to 7 records or so might be corrupted.
However, something doesn't add up... Please see below:
-- I have narrowed down the row
SELECT * FROM table ORDER BY table_id OFFSET 526199 LIMIT 1 -- ERROR: invalid memory alloc request size 1765277700
-- I was able to view a few columns
SELECT table_id, table_column1, table_column2 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 -- returns one row table_id = 12345
-- using that id to SELECT ALL. It shows fine...I was assuming this will give me same error...
SELECT * FROM table WHERE table_id=12345 -- shows perfectly
-- This also returns value just fine
SELECT table_column3 FROM table WHERE table_id = 12345
-- However this returns an error
SELECT table_column3 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 --error ERROR: invalid memory alloc request size 1765277700
To me it is as if it is accessing to different record.. I thought possibly primary key index might be corrupted.. so attempted
REINDEX TABLE table;
--ERROR: could not create unique index "table_pkey"
--DETAIL: Table contains duplicated values.
-- Now this returns 0 row... Not sure what I am doing wrong here...
SELECT table_id FROM table GROUP BY table_id HAVING count(table_id) > 1
If anyone could advice me why I am able to view record with primary key query but not with OFFSET?
Do I consider these record as corrupted?
If so deleting these records might resolve the error I am getting?
Thank you very much for your time.
--
Naoko
Curious: was there some sort of hardware issue or anything like that preceding this issue?
There was an hardware crash.
after that pg_dump failed with an error:
ERROR: invalid memory alloc request size 1765277700
I searched archive and it indicates that this is data corruption.
I have narrowed down to 7 records or so might be corrupted.
However, something doesn't add up... Please see below:
-- I have narrowed down the row
SELECT * FROM table ORDER BY table_id OFFSET 526199 LIMIT 1 -- ERROR: invalid memory alloc request size 1765277700
-- I was able to view a few columns
SELECT table_id, table_column1, table_column2 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 -- returns one row table_id = 12345
-- using that id to SELECT ALL. It shows fine...I was assuming this will give me same error...
SELECT * FROM table WHERE table_id=12345 -- shows perfectly
-- This also returns value just fine
SELECT table_column3 FROM table WHERE table_id = 12345
-- However this returns an error
SELECT table_column3 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 --error ERROR: invalid memory alloc request size 1765277700
To me it is as if it is accessing to different record.. I thought possibly primary key index might be corrupted.. so attempted
REINDEX TABLE table;
--ERROR: could not create unique index "table_pkey"
--DETAIL: Table contains duplicated values.
-- Now this returns 0 row... Not sure what I am doing wrong here...
SELECT table_id FROM table GROUP BY table_id HAVING count(table_id) > 1
If anyone could advice me why I am able to view record with primary key query but not with OFFSET?
Do I consider these record as corrupted?
If so deleting these records might resolve the error I am getting?
Thank you very much for your time.
--
Naoko
Naoko Reeves <naokoreeves@gmail.com> writes:
> [ inconsistent behavior with a corrupted table ]
I think most likely some of these queries are using a corrupted index
and some are not --- have you looked at the EXPLAIN for each one?
It might be a good idea to turn off enable_indexscan and
enable_bitmapscan while poking at the table.
regards, tom lane
On Fri, Feb 24, 2012 at 10:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Naoko Reeves <naokoreeves@gmail.com> writes: >> [ inconsistent behavior with a corrupted table ] > > I think most likely some of these queries are using a corrupted index > and some are not --- have you looked at the EXPLAIN for each one? > It might be a good idea to turn off enable_indexscan and > enable_bitmapscan while poking at the table. Could it also be a corrupted toast table?
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Fri, Feb 24, 2012 at 10:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Naoko Reeves <naokoreeves@gmail.com> writes:
>>> [ inconsistent behavior with a corrupted table ]
>> I think most likely some of these queries are using a corrupted index
>> and some are not --- have you looked at the EXPLAIN for each one?
>> It might be a good idea to turn off enable_indexscan and
>> enable_bitmapscan while poking at the table.
> Could it also be a corrupted toast table?
The toast table might well be corrupted, but that wouldn't explain the
variation in behavior when fetching the "same" row. I'm inclined to
think that Naoko has got more than one apparently-live row with the
same id value, as a result of corruption of xmin/xmax values or commit
hint bits. Not sure how to explain all the symptoms though.
regards, tom lane
On Fri, Feb 24, 2012 at 4:01 AM, Naoko Reeves <naokoreeves@gmail.com> wrote: > -- I have narrowed down the row > SELECT * FROM table ORDER BY table_id OFFSET 526199 LIMIT 1 -- ERROR: > invalid memory alloc request size 1765277700 Are you certain that offset 526199 is using both the same query plan and doesn't produce this error? > -- I was able to view a few columns > SELECT table_id, table_column1, table_column2 FROm table ORDER BY table_id > OFFSET 526199 LIMIT 1 -- returns one row table_id = 12345 > -- using that id to SELECT ALL. It shows fine...I was assuming this will > give me same error... > SELECT * FROM table WHERE table_id=12345 -- shows perfectly > -- This also returns value just fine > SELECT table_column3 FROM table WHERE table_id = 12345 > -- However this returns an error > SELECT table_column3 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 > --error ERROR: invalid memory alloc request size 1765277700 If you do select <col_list> from table where col_list is all cols except col3 do you get the error? What type of col is col3?
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
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
On Fri, Feb 24, 2012 at 1:25 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Fri, Feb 24, 2012 at 4:01 AM, Naoko Reeves <naokoreeves@gmail.com> wrote:Are you certain that offset 526199 is using both the same query plan
> -- I have narrowed down the row
> SELECT * FROM table ORDER BY table_id OFFSET 526199 LIMIT 1 -- ERROR:
> invalid memory alloc request size 1765277700
and doesn't produce this error?If you do select <col_list> from table
> -- I was able to view a few columns
> SELECT table_id, table_column1, table_column2 FROm table ORDER BY table_id
> OFFSET 526199 LIMIT 1 -- returns one row table_id = 12345
> -- using that id to SELECT ALL. It shows fine...I was assuming this will
> give me same error...
> SELECT * FROM table WHERE table_id=12345 -- shows perfectly
> -- This also returns value just fine
> SELECT table_column3 FROM table WHERE table_id = 12345
> -- However this returns an error
> SELECT table_column3 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1
> --error ERROR: invalid memory alloc request size 1765277700
where col_list is all cols except col3 do you get the error? What
type of col is col3?
--
Naoko Reeves