Thread: Incomplete pg_dump operation
A shell script runs pg_dump once each day to backup a Postgresql database and then compress it for storage. The script
andbackup process worked flawlessly for years, but now returns these error messages:
pg_dump: ERROR: could not open relation with OID 2196359751
pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR: could not open relation with OID 2196359751
pg_dump: The command was: COPY public.xyz ({various_field_names}) TO stdout;
The pg_dump command halts with these error messages after copying or dumping about 50% of the database. Accordingly,
onlya fraction of the database is actually being backed up.
Is there corruption of some type inside my PostgreSQL database? Has an index or something similar become corrupted?
Thinking more about corruption of some type inside my database and whether an index has become corrupted, I investigatedthe REINDEX command described at pp. 288 and 847-849 of the 1335-page PostgreSQL 7.4.2 Documentation manual at <http://www.postgresql.org/files/documentation/pdf/7.4/postgresql-7.4.2-US.pdf>http://www.postgresql.org/files/documentation/pdf/7.4/postgresql-7.4.2-US.pdf This morning, I executed a: REINDEX DATABASE database_name FORCE; command, and received a bunch of: NOTICE: table "pg_xxxxx" was reindexed messages -- which all look just fine. I then executed a: REINDEX TABLE xyz; command -- using the table name pg_dump was complaining about yesterday -- and received this message: ERROR: could not open relation with OID 2196359751 making reference to the same OID pg_dump complained about yesterday. How do I identify this offensive data record that's causing so much disruption and, perhaps, delete it? ------- At 10:08 AM 2/6/2010, peter@vfemail.net wrote: >A shell script runs pg_dump once each day to backup a Postgresql database and then compress it for storage. The scriptand backup process worked flawlessly for years, but now returns these error messages: > > pg_dump: ERROR: could not open relation with OID 2196359751 > pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed. > pg_dump: Error message from server: ERROR: could not open relation with OID 2196359751 > pg_dump: The command was: COPY public.xyz ({various_field_names}) TO stdout; > >The pg_dump command halts with these error messages after copying or dumping about 50% of the database. Accordingly, onlya fraction of the database is actually being backed up. > >Is there corruption of some type inside my PostgreSQL database? Has an index or something similar become corrupted?
Done. The command generates no response. I'm returned to a command prompt. If I append a semicolon to the command, I geta syntax error. ----- At 07:39 AM 2/7/2010, Francisco Leovey wrote: >Just do a > SELECT * from xyz where OID = 2196359751 > >--- On Sun, 2/7/10, peter@vfemail.net <peter@vfemail.net> wrote: >From: peter@vfemail.net <peter@vfemail.net> >Subject: Re: [NOVICE] Incomplete pg_dump operation >To: pgsql-novice@postgresql.org >Date: Sunday, February 7, 2010, 9:36 AM > >Thinking more about corruption of some type inside my database and whether an index has become corrupted, I investigatedthe REINDEX command described at pp. 288 and 847-849 of the 1335-page PostgreSQL 7.4.2 Documentation manual at <<http://www.postgresql.org/files/documentation/pdf/7.4/postgresql-7.4.2-US.pdf>http://www.postgresql.org/files/documentation/pdf/7.4/postgresql-7.4.2-US.pdf>http://www.postgresql.org/files/documentation/pdf/7.4/postgresql-7.4.2-US.pdf >This morning, I executed a: > REINDEX DATABASE database_name FORCE; >command, and received a bunch of: > NOTICE: table "pg_xxxxx" was reindexed >messages -- which all look just fine. >I then executed a: > REINDEX TABLE xyz; >command -- using the table name pg_dump was complaining about yesterday -- and received this message: > ERROR: could not open relation with OID 2196359751 >making reference to the same OID pg_dump complained about yesterday. >How do I identify this offensive data record that's causing so much disruption and, perhaps, delete it? >------- >At 10:08 AM 2/6/2010, <http://us.mc517.mail.yahoo.com/mc/compose?to=peter@vfemail.net>peter@vfemail.net wrote: >>A shell script runs pg_dump once each day to backup a Postgresql database and then compress it for storage. The scriptand backup process worked flawlessly for years, but now returns these error messages: >> >> pg_dump: ERROR: could not open relation with OID 2196359751 >> pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed. >> pg_dump: Error message from server: ERROR: could not open relation with OID 2196359751 >> pg_dump: The command was: COPY public.xyz ({various_field_names}) TO stdout; >> >>The pg_dump command halts with these error messages after copying or dumping about 50% of the database. Accordingly, onlya fraction of the database is actually being backed up. >> >>Is there corruption of some type inside my PostgreSQL database? Has an index or something similar become corrupted? > > >-- >Sent via pgsql-novice mailing list (<http://us.mc517.mail.yahoo.com/mc/compose?to=pgsql-novice@postgresql.org>pgsql-novice@postgresql.org) >To make changes to your subscription: ><http://www.postgresql.org/mailpref/pgsql-novice>http://www.postgresql.org/mailpref/pgsql-novice
peter@vfemail.net writes:
> I then executed a:
> REINDEX TABLE xyz;
> command -- using the table name pg_dump was complaining about yesterday -- and received this message:
> ERROR: could not open relation with OID 2196359751
> making reference to the same OID pg_dump complained about yesterday.
Do you get anything from "select * from pg_class where oid = 2196359751;" ?
regards, tom lane
Yes. The:
select * from pg_class where oid = 2196359751;
command returns:
relname | relnamespace | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid |
reltoastidxid| relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs
|relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
---------+--------------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
(0 rows)
-------
At 11:29 AM 2/7/2010, Tom Lane wrote:
>peter@vfemail.net writes:
>> I then executed a:
>> REINDEX TABLE xyz;
>> command -- using the table name pg_dump was complaining about yesterday -- and received this message:
>> ERROR: could not open relation with OID 2196359751
>> making reference to the same OID pg_dump complained about yesterday.
>
>Do you get anything from "select * from pg_class where oid = 2196359751;" ?
>
> regards, tom lane
peter@vfemail.net writes:
> At 11:29 AM 2/7/2010, Tom Lane wrote:
>> Do you get anything from "select * from pg_class where oid = 2196359751;" ?
> Yes. The:
> select * from pg_class where oid = 2196359751;
> command returns [ nothing ]
OK, well that explains why it can't open such a relation ;-). The next
question is why is it trying to. My first guess is that there is a
dangling link in pg_index, ie you once had an index with such an OID
but something happened to it. Please try
select * from pg_index where indexrelid = 2196359751;
and if that gets a hit, then select the pg_class row with the OID
shown as indrelid.
regards, tom lane
The first execution of the:
select * from pg_index where indexrelid = 2196359751;
command returned this message:
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because
anotherserver process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
A second execution of this command returns a syntax error complaining about the semicolon.
A third execution of this command without the semicolon returns nothing.
After executing these three commands, I exited Postgresql and ran the pg_dump script. The database backup process
terminatedwith these messages:
pg_dump: ERROR: could not open relation with OID 2196359751
pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR: could not open relation with OID 2196359751
pg_dump: The command was: COPY public.xyz ({various field names}) TO stdout;
-------
At 01:19 AM 2/8/2010, Tom Lane wrote:
>peter@vfemail.net writes:
>> At 11:29 AM 2/7/2010, Tom Lane wrote:
>>> Do you get anything from "select * from pg_class where oid = 2196359751;" ?
>
>> Yes. The:
>> select * from pg_class where oid = 2196359751;
>> command returns [ nothing ]
>
>OK, well that explains why it can't open such a relation ;-). The next
>question is why is it trying to. My first guess is that there is a
>dangling link in pg_index, ie you once had an index with such an OID
>but something happened to it. Please try
>
> select * from pg_index where indexrelid = 2196359751;
>
>and if that gets a hit, then select the pg_class row with the OID
>shown as indrelid.
>
> regards, tom lane
Just for the heck of it, I entered the database again and issued the:
select * from pg_index where indexrelid = 2196359751;
a fourth time. This time, the command returns this message:
indexrelid | indrelid | indkey | indclass | indnatts | indisunique | indisprimary | indisclustered | indexprs |
indpred
------------+----------+--------+----------+----------+-------------+--------------+----------------+----------+---------
(0 rows)
------
At 02:07 AM 2/8/2010, peter@vfemail.net wrote:
>The first execution of the:
>
> select * from pg_index where indexrelid = 2196359751;
>
>command returned this message:
>
> WARNING: terminating connection because of crash of another server process
> DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because
anotherserver process exited abnormally and possibly corrupted shared memory.
> HINT: In a moment you should be able to reconnect to the database and repeat your command.
>server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Succeeded.
>
>A second execution of this command returns a syntax error complaining about the semicolon.
>
>A third execution of this command without the semicolon returns nothing.
>
>After executing these three commands, I exited Postgresql and ran the pg_dump script. The database backup process
terminatedwith these messages:
>
> pg_dump: ERROR: could not open relation with OID 2196359751
> pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed.
> pg_dump: Error message from server: ERROR: could not open relation with OID 2196359751
> pg_dump: The command was: COPY public.xyz ({various field names}) TO stdout;
>
>-------
>
>At 01:19 AM 2/8/2010, Tom Lane wrote:
>>peter@vfemail.net writes:
>>> At 11:29 AM 2/7/2010, Tom Lane wrote:
>>>> Do you get anything from "select * from pg_class where oid = 2196359751;" ?
>>
>>> Yes. The:
>>> select * from pg_class where oid = 2196359751;
>>> command returns [ nothing ]
>>
>>OK, well that explains why it can't open such a relation ;-). The next
>>question is why is it trying to. My first guess is that there is a
>>dangling link in pg_index, ie you once had an index with such an OID
>>but something happened to it. Please try
>>
>> select * from pg_index where indexrelid = 2196359751;
>>
>>and if that gets a hit, then select the pg_class row with the OID
>>shown as indrelid.
>>
>> regards, tom lane
>
>
>
>--
>Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-novice
peter@vfemail.net writes:
> The first execution of the:
> select * from pg_index where indexrelid = 2196359751;
> command returned this message:
> WARNING: terminating connection because of crash of another server process
It seems you have much worse problems than this one table. I think you
need to plan on re-initdb'ing and re-loading. If you can't get pg_dump
to produce a whole dump, does it work to dump one table at a time with
the -t switch?
regards, tom lane
xyz is the largest table in the database, and the one identified in prior error messages.
After su'ing to pgsql, I executed a:
pg_dump -t xyz database_name > /usr/local/pgsql/db_backups/database_name-Manual-Xyz-Table-Dump
command, and this message was returned:
pg_dump: ERROR: could not open relation with OID 2196359751
pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR: could not open relation with OID 2196359751
pg_dump: The command was: COPY public.xyz ({various field names}) TO stdout;
That's the same OID other error messages have complained about.
About 1/3 of the database was dumped to the destination file before this error message was displayed.
-------
At 10:37 AM 2/8/2010, Tom Lane wrote:
>peter@vfemail.net writes:
>> The first execution of the:
>
>> select * from pg_index where indexrelid = 2196359751;
>
>> command returned this message:
>
>> WARNING: terminating connection because of crash of another server process
>
>It seems you have much worse problems than this one table. I think you
>need to plan on re-initdb'ing and re-loading. If you can't get pg_dump
>to produce a whole dump, does it work to dump one table at a time with
>the -t switch?
>
> regards, tom lane
peter@vfemail.net writes:
> After su'ing to pgsql, I executed a:
> pg_dump -t xyz database_name > /usr/local/pgsql/db_backups/database_name-Manual-Xyz-Table-Dump
> command, and this message was returned:
> pg_dump: ERROR: could not open relation with OID 2196359751
> pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed.
> pg_dump: Error message from server: ERROR: could not open relation with OID 2196359751
> pg_dump: The command was: COPY public.xyz ({various field names}) TO stdout;
> That's the same OID other error messages have complained about.
Hmm. AFAICS, a COPY OUT operation should only try to read the given
table (well, and some system tables, but those have small OIDs). The
idea that comes to mind now is that you have a corrupt TOAST pointer
--- or at least data that the code is taking as a TOAST pointer ---
in the xyz table. If that contained 2196359751 in its va_toastrelid
field, that would produce the observed symptoms.
If this theory is correct then there is one damaged row, or maybe
even just one damaged field in a row, somewhere in xyz. (Or maybe
there is more damage after the first one, but anyway it's probably
somewhat localized.) If you can delete the damaged row(s) then you
should be able to dump the rest of the data, which will be a big
step forward from where you are now.
You should be able to home in on the location of the damaged row by
doing "select * from xyz limit N" for various N and seeing what's
the largest N that doesn't fail. Then "select ctid from xyz
offset N limit 1" should give you the ctid of the damaged row
--- confirm by seeing that "select * from xyz where ctid = 'whatever'"
does fail.
After that, you could try "delete from xyz where ctid = 'whatever'" but
I expect that this will fail just like selecting it does. What you'll
probably have to do is stop the postmaster and manually zero the block
containing the row with dd or similar tool. If you search the postgres
archives for previous discussions of recovering from corrupted data, you
should find lots of details about this type of process. It doesn't
come up often enough for anyone to have tried to automate it though.
It might also be interesting to get a dump of the damaged block
(see pg_filedump) so we can try to get an idea of exactly what
happened.
regards, tom lane
You are lightyears ahead of me, Mr. Lane.
Your conceptual framework of identifying the offensive data record and deleting it makes a lot of sense to me. I am,
unfortunately,illiterate about what commands I need to execute to make that happen.
Every "select * from news limit N" command, for N = 0 to 9999999999, appears to generate no response.
None of these commands return any message of any kind:
select * from news limit 1000000000
select * from news limit 1000
select * from news limit 100
select * from news limit 10
select * from news limit 10111
select * from news limit 2196359751
select * from news limit 4000000000
select * from news limit 300000000
select * from news limit 30000000
select * from news limit 9999999999
select * from news limit 999999999
select * from news limit 99999999
select * from news limit 9999999
select * from news limit 999999
select * from news limit 99999
select * from news limit 9999
select * from news limit 999
select * from news limit 99
select * from news limit 9
select * from news limit 1
select * from news limit 0
Have I misunderstood what you told me to do?
I will investigate the pg_filedump command tomorrow morning.
-------
At 06:04 PM 2/8/2010, Tom Lane wrote:
>peter@vfemail.net writes:
>> After su'ing to pgsql, I executed a:
>
>> pg_dump -t xyz database_name > /usr/local/pgsql/db_backups/database_name-Manual-Xyz-Table-Dump
>
>> command, and this message was returned:
>
>> pg_dump: ERROR: could not open relation with OID 2196359751
>> pg_dump: SQL command to dump the contents of table "xyz" failed: PQendcopy() failed.
>> pg_dump: Error message from server: ERROR: could not open relation with OID 2196359751
>> pg_dump: The command was: COPY public.xyz ({various field names}) TO stdout;
>
>> That's the same OID other error messages have complained about.
>
>Hmm. AFAICS, a COPY OUT operation should only try to read the given
>table (well, and some system tables, but those have small OIDs). The
>idea that comes to mind now is that you have a corrupt TOAST pointer
>--- or at least data that the code is taking as a TOAST pointer ---
>in the xyz table. If that contained 2196359751 in its va_toastrelid
>field, that would produce the observed symptoms.
>
>If this theory is correct then there is one damaged row, or maybe
>even just one damaged field in a row, somewhere in xyz. (Or maybe
>there is more damage after the first one, but anyway it's probably
>somewhat localized.) If you can delete the damaged row(s) then you
>should be able to dump the rest of the data, which will be a big
>step forward from where you are now.
>
>You should be able to home in on the location of the damaged row by
>doing "select * from xyz limit N" for various N and seeing what's
>the largest N that doesn't fail. Then "select ctid from xyz
>offset N limit 1" should give you the ctid of the damaged row
>--- confirm by seeing that "select * from xyz where ctid = 'whatever'"
>does fail.
>
>After that, you could try "delete from xyz where ctid = 'whatever'" but
>I expect that this will fail just like selecting it does. What you'll
>probably have to do is stop the postmaster and manually zero the block
>containing the row with dd or similar tool. If you search the postgres
>archives for previous discussions of recovering from corrupted data, you
>should find lots of details about this type of process. It doesn't
>come up often enough for anyone to have tried to automate it though.
>
>It might also be interesting to get a dump of the damaged block
>(see pg_filedump) so we can try to get an idea of exactly what
>happened.
>
> regards, tom lane
peter@vfemail.net writes:
> None of these commands return any message of any kind:
> select * from news limit 1000000000
> select * from news limit 1000
> select * from news limit 100
> select * from news limit 10
Um .... maybe you're forgetting to terminate them with a semicolon?
regards, tom lane
The semicolon makes a tremendous difference. Thank you for your patience. select * from news limit 0; displays a nice little table and 0 rows of data. select * from news limit 1; displays the same table and the contents of 1 data record. select * from news limit 5000000000; returns an "ERROR: integer out of range" message. select * from news limit 3000000000; also returns an "ERROR: integer out of range" message. select * from news limit 2000000000; returns the too-familiar "ERROR: could not open relation with OID 2196359751" message. select * from news limit 1000000000; also returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 500000000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 250000000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 125000000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 62500000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 31250000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 15625000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 7812500; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 3906250; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 1953125; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 1000000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 500000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 250000; returns an "out of memory for query result" message. select * from news limit 375000; returns an "out of memory for query result" message. select * from news limit 437500; returns an "out of memory for query result" message. select * from news limit 468750; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 453125; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 445312; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 441406; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439453; returns an "out of memory for query result" message. select * from news limit 440429; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439941; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439697; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439575; returns an "out of memory for query result" message. select * from news limit 439636; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439605; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439590; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439582; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439579; returns an "out of memory for query result" message. select * from news limit 439581; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. I have repeated and and confirmed these results: select * from news limit 439579; returns an "out of memory for query result" message. select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. ------- At 10:11 PM 2/8/2010, Tom Lane wrote: >peter@vfemail.net writes: >> None of these commands return any message of any kind: > >> select * from news limit 1000000000 >> select * from news limit 1000 >> select * from news limit 100 >> select * from news limit 10 > >Um .... maybe you're forgetting to terminate them with a semicolon? > > regards, tom lane > >-- >Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-novice
The semicolon makes a tremendous difference. Thank you for your patience. select * from news limit 0; displays a nice little table and 0 rows of data. select * from news limit 1; displays the same table and the contents of 1 data record. select * from news limit 5000000000; returns an "ERROR: integer out of range" message. select * from news limit 3000000000; also returns an "ERROR: integer out of range" message. select * from news limit 2000000000; returns the too-familiar "ERROR: could not open relation with OID 2196359751" message. select * from news limit 1000000000; also returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 500000000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 250000000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 125000000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 62500000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 31250000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 15625000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 7812500; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 3906250; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 1953125; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 1000000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 500000; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 250000; returns an "out of memory for query result" message. select * from news limit 375000; returns an "out of memory for query result" message. select * from news limit 437500; returns an "out of memory for query result" message. select * from news limit 468750; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 453125; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 445312; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 441406; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439453; returns an "out of memory for query result" message. select * from news limit 440429; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439941; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439697; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439575; returns an "out of memory for query result" message. select * from news limit 439636; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439605; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439590; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439582; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439579; returns an "out of memory for query result" message. select * from news limit 439581; returns an "ERROR: could not open relation with OID 2196359751" message. select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. I have repeated and and confirmed these results: select * from news limit 439579; returns an "out of memory for query result" message. select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. ------- At 10:11 PM 2/8/2010, Tom Lane wrote: >peter@vfemail.net writes: >> None of these commands return any message of any kind: > >> select * from news limit 1000000000 >> select * from news limit 1000 >> select * from news limit 100 >> select * from news limit 10 > >Um .... maybe you're forgetting to terminate them with a semicolon? > > regards, tom lane > >-- >Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-novice
The semicolon makes a tremendous difference. Thank you for your patience. select * from news limit 0; displays a nice little table and 0 rows of data. select * from news limit 1; displays the same table and the contents of 1 data record. select * from news limit 5000000000; returns an "ERROR: integer out of range" message. select * from news limit 439579; returns an "out of memory for query result" message. select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. ------- At 10:11 PM 2/8/2010, Tom Lane wrote: >peter@vfemail.net writes: >> None of these commands return any message of any kind: > >> select * from news limit 1000000000 >> select * from news limit 1000 >> select * from news limit 100 >> select * from news limit 10 > >Um .... maybe you're forgetting to terminate them with a semicolon? > > regards, tom lane
On 9 February 2010 14:12, <peter@vfemail.net> wrote: > > The semicolon makes a tremendous difference. Thank you for your patience. > > select * from news limit 0; displays a nice little table and 0 rows of data. > select * from news limit 1; displays the same table and the contents of 1 data record. [...] > select * from news limit 439579; returns an "out of memory for query result" message. > select * from news limit 439581; returns an "ERROR: could not open relation with OID 2196359751" message. > select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. > > I have repeated and and confirmed these results: > > select * from news limit 439579; returns an "out of memory for query result" message. > select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. I don't know why you get out of memory errors for some and not others. Try finding the largest "limit" that actually returns data as Tom said. e.g. assume the largest value for limit that works for you is 100000. Then "select ctid from news offset 100000 limit 1;" (substituting the real value for 100000). Assume the value returned for ctid is (19,32). Then the following SHOULD also give you the "could not open relation..." error: select * from news where ctid = '(19,32)'; This basically identifies the row (hopefully just one) that's causing the trouble. By the way, if I were you I'd shut down PostgreSQL and get a copy of the data directory just in case anything else goes wrong while trying to recover from this issue. -- Michael Wood <esiotrot@gmail.com>
The semicolon makes a tremendous difference. Thank you for your patience. select * from news limit 0; displays a nice little table and 0 rows of data. select * from news limit 1; displays the same table and the contents of 1 data record. select * from news limit 5000000000; returns an "ERROR: integer out of range" message. select * from news limit 439579; returns an "out of memory for query result" message. select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. ------- At 10:11 PM 2/8/2010, Tom Lane wrote: >peter@vfemail.net writes: >> None of these commands return any message of any kind: > >> select * from news limit 1000000000 >> select * from news limit 1000 >> select * from news limit 100 >> select * from news limit 10 > >Um .... maybe you're forgetting to terminate them with a semicolon? > > regards, tom lane
Our PostgreSQL problem was too far beyond my abilities to solve it, so I solicited bids using RentACoder.com. This problem was solved once and for all this morning by Frank Heikens at <http://nl.linkedin.com/pub/frank-heikens/0/190/517>http://nl.linkedin.com/pub/frank-heikens/0/190/517and <http://www.rentacoder.com/RentACoder/DotNet/SoftwareCoders/ShowBioInfo.aspx?lngAuthorId=7514248>http://www.rentacoder.com/RentACoder/DotNet/SoftwareCoders/ShowBioInfo.aspx?lngAuthorId=7514248 --and our database system's back to normal. Mr. Heikens isolated the one corrupted data record, created a new table, and replaced the flawed table with the new table. I have nothing but compliments for Mr. Heikens' knowledge, professionalism, speed, accuracy, caution, communication,and wizardry. ------- At 10:16 AM 2/9/2010, peter@vfemail.net wrote: >The semicolon makes a tremendous difference. Thank you for your patience. > >select * from news limit 0; displays a nice little table and 0 rows of data. >select * from news limit 1; displays the same table and the contents of 1 data record. >select * from news limit 5000000000; returns an "ERROR: integer out of range" message. > >select * from news limit 439579; returns an "out of memory for query result" message. >select * from news limit 439580; returns an "ERROR: could not open relation with OID 2196359751" message. > >------- > >At 10:11 PM 2/8/2010, Tom Lane wrote: >>peter@vfemail.net writes: >>> None of these commands return any message of any kind: >> >>> select * from news limit 1000000000 >>> select * from news limit 1000 >>> select * from news limit 100 >>> select * from news limit 10 >> >>Um .... maybe you're forgetting to terminate them with a semicolon? >> >> regards, tom lane > > > > >-- >Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-novice