Re: Incomplete pg_dump operation

On 9 February 2010 14:12, <peter(at)vfemail(dot)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(at)gmail(dot)com>