A shell script runs pg_dump once each day to backup a Postgresql database and then compress it for storage. The script and 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, only a 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?

Search Discussions

  • Peter at Feb 7, 2010 at 11:36 am
    Thinking more about corruption of some type inside my database and whether an index has become corrupted, I investigated the 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 script and 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, only a 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?
  • Peter at Feb 7, 2010 at 1:53 pm
    Done. The command generates no response. I'm returned to a command prompt. If I append a semicolon to the command, I get a 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 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 investigated the 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 script and 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, only a 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
  • Tom Lane at Feb 7, 2010 at 4:30 pm

    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 at Feb 7, 2010 at 10:04 pm
    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
  • Tom Lane at Feb 8, 2010 at 6:20 am

    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
  • Peter at Feb 8, 2010 at 7:10 am
    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 another server 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 terminated with 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
  • Peter at Feb 8, 2010 at 7:16 am
    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 another server 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 terminated with 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
  • Tom Lane at Feb 8, 2010 at 3:38 pm

    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 at Feb 8, 2010 at 6:35 pm
    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
  • Tom Lane at Feb 8, 2010 at 11:05 pm

    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 at Feb 9, 2010 at 2:31 am
    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
  • Tom Lane at Feb 9, 2010 at 3:13 am

    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
  • Peter at Feb 9, 2010 at 2:59 pm
    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
  • Peter at Feb 9, 2010 at 2:51 pm
    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
  • Michael Wood at Feb 9, 2010 at 3:46 pm

    On 9 February 2010 14:12, 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>
  • Peter at Feb 9, 2010 at 3:02 pm
    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
  • Peter at Apr 11, 2010 at 12:38 pm
    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/517 and <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
  • Peter at Feb 9, 2010 at 3:57 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 6, '10 at 3:09p
activeApr 11, '10 at 12:38p
posts19
users3
websitepostgresql.org
irc#postgresql

3 users in discussion

Peter: 13 posts Tom Lane: 5 posts Michael Wood: 1 post

People

Translate

site design / logo © 2021 Grokbase