pg_dump problem

On Wed, 2007-11-14 at 10:32 -0800, SHARMILA JOTHIRAJAH wrote:

Hi
I try to use pg_dump to dump my database.
pg_dump smrs
and it gives me an error
pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite
entry OID 670741 not found

check out the --oids option in the manuals ("man pg_dump")... could
help ?!

and try this:

pg_dump --oids smrs

Cheers
jmf

SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> writes:

pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found

What causes this problem?

Corrupt system tables, looks like :-( What PG version is this?

I would suggest checking to see if either of those OIDs appears in
either the objid or refobjid columns of pg_depend. If not, the
most likely theory is that this pg_rewrite entry somehow didn't
get deleted when its parent table was dropped. You could just
delete it manually, eg,
delete from pg_rewrite where oid = 670741;

If you do find other traces of the table it might be better to try
to resurrect the table (actually it's most likely a view not a
table).

regards, tom lane

Hi,
Thanks...But I still get the same error

pg_dump --oids smrs
pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found

sharmila

----- Original Message ----
From: Joao Miguel Ferreira <joao.mc.ferreira@criticalsoftware.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, November 14, 2007 1:41:56 PM
Subject: Re: [GENERAL] pg_dump problem

On Wed, 2007-11-14 at 10:32 -0800, SHARMILA JOTHIRAJAH wrote:

Hi
I try to use pg_dump to dump my database.
pg_dump smrs
and it gives me an error
pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite
entry OID 670741 not found

check out the --oids option in the manuals ("man pg_dump")... could
help ?!

and try this:

pg_dump --oids smrs

Cheers
jmf

What causes this problem?
Thanks
sharmila

______________________________________________________________________

Be a better sports nut! Let your teams follow you with Yahoo Mobile.
Try it now.

---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/

Hi
Thanks

PG version is 8.2.3
I queried the pg_depend using this query
select * from pg_depend where objid in (670739,670741) or refobjid in (670739,670741)
looks like the OIDs are there....
classid objid objsubid refclassid refobjid refobjsubid deptype
---------- -------- ----------- ------------- ----------- -------------- ----------
1247 670740 0 1259 670739 0 i
1259 670739 0 2615 2200 0 n
2618 670741 0 1259 670739 0 i
2618 670741 0 1259 670645 3 n
2618 670741 0 1259 670648 3 n
2618 670741 0 1259 670739 0 n

so what else can cause tis problem?
sharmila

----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, November 14, 2007 2:21:03 PM
Subject: Re: [GENERAL] pg_dump problem

SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> writes:

pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite

entry OID 670741 not found

What causes this problem?

Corrupt system tables, looks like :-( What PG version is this?

I would suggest checking to see if either of those OIDs appears in
either the objid or refobjid columns of pg_depend. If not, the
most likely theory is that this pg_rewrite entry somehow didn't
get deleted when its parent table was dropped. You could just
delete it manually, eg,
delete from pg_rewrite where oid = 670741;

If you do find other traces of the table it might be better to try
to resurrect the table (actually it's most likely a view not a
table).

regards, tom lane

____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/

SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> writes:

looks like the OIDs are there....

Yeah, that makes it look more like the pg_class row went missing
than that there was an intentional drop of the view.

Does "VACUUM VERBOSE pg_class" report anything interesting?
It's possible also that reindexing pg_class would fix it.

regards, tom lane

You are right. There are no rows in pg_class with oids 670739 and 670741. Is that the problem? How do I fix that. Will deleting those rows from pg_depend fix this problem?
Also I have another question. Should the pg tables (like pg_class etc) generally be vacuumed regularly?
Thanks
sharmila

----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, November 14, 2007 3:33:20 PM
Subject: Re: [GENERAL] pg_dump problem

SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> writes:

looks like the OIDs are there....

Yeah, that makes it look more like the pg_class row went missing
than that there was an intentional drop of the view.

Does "VACUUM VERBOSE pg_class" report anything interesting?
It's possible also that reindexing pg_class would fix it.

regards, tom lane

____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/

On Wed, Nov 14, 2007 at 10:32:54AM -0800, SHARMILA JOTHIRAJAH wrote:

Hi
I try to use pg_dump to dump my database.
pg_dump smrs
and it gives me an error
pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found

Is there any possibility you have Slony installed? Is this a replica? If
so, it's a well-known problem. You can't use pg_dump under those
circumstances.

A

--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

No...I dont have slony installed. Its not a replica
sharmila

----- Original Message ----
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-general@postgresql.org
Sent: Thursday, November 15, 2007 1:03:48 PM
Subject: Re: [GENERAL] pg_dump problem

On Wed, Nov 14, 2007 at 10:32:54AM -0800, SHARMILA JOTHIRAJAH wrote:

Hi
I try to use pg_dump to dump my database.
pg_dump smrs
and it gives me an error
pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite

entry OID 670741 not found

Is there any possibility you have Slony installed? Is this a replica?
If
so, it's a well-known problem. You can't use pg_dump under those
circumstances.

A

--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/

Hi
The dump works now after deleting those rows from the pg_rewrite table
Thanks for your help
sharmila

----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, November 14, 2007 3:33:20 PM
Subject: Re: [GENERAL] pg_dump problem

SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> writes:

looks like the OIDs are there....

Yeah, that makes it look more like the pg_class row went missing
than that there was an intentional drop of the view.

Does "VACUUM VERBOSE pg_class" report anything interesting?
It's possible also that reindexing pg_class would fix it.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your
message can get through to the mailing list cleanly

____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/