Thread: pg_dump: schema with OID 58698 does not exist
howdy all, I'm getting the above error in one of my dev DBs. I've read in the archives that to stop the error from happening I can just delete entries in pg_type and pg_class, however there seemed to be some community interest in doing some debugging. (mentioned in this thread: http://archives.postgresql.org/pgsql-bugs/2010-01/msg00090.php ) This is PG 9.0.2 on RedHat 64 bit. Is there anything you'd like me to check before i fix the problem? (since it's dev, i plan to just wack the database and re-create it.. so i don't need to worry about if that particular object was used or whatever... ) Thanks Dave
David Kerr <dmk@mr-paradox.net> writes:
> I'm getting the above error in one of my dev DBs.
Would you poke around in the system catalogs and find where the dangling
reference is located? Have you got any idea of how to reproduce this
failure from a standing start?
regards, tom lane
On Tue, Feb 08, 2011 at 10:16:02PM -0500, Tom Lane wrote: - David Kerr <dmk@mr-paradox.net> writes: - > I'm getting the above error in one of my dev DBs. - - Would you poke around in the system catalogs and find where the dangling - reference is located? Have you got any idea of how to reproduce this - failure from a standing start? - - regards, tom lane - Ok i'll dig around. I spoke with the developer, he claims not to have done anything to the DB. so i don't know how to reproduce the state. (but i do get the error every time i use pg_dump on the DB). it seems that this DB is mainly used as a target for junit testing. Also, i don't know if this is related but I'm seeing quite a few: Feb 9 17:04:17 db001 postgres-9[21285]: [28-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4223 Feb 9 17:04:17 db001 postgres-9[21285]: [29-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4224 Feb 9 17:04:17 db001 postgres-9[21285]: [30-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4225 Feb 9 17:04:17 db001 postgres-9[21285]: [31-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4226 Feb 9 17:04:17 db001 postgres-9[21285]: [32-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4227 Feb 9 17:04:17 db001 postgres-9[21285]: [33-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4228 Feb 9 17:04:17 db001 postgres-9[21285]: [34-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4229 It seems like a system process is reporting it, i tried to vacuum all of my DBs to try to narrow down which DB it's in - didn't generate the error. I suppose it's possible that the process is fixing the data and then it gets broken again. Any pointers would be helpful! Thanks Dave
On Wed, Feb 09, 2011 at 09:42:36AM -0800, David Kerr wrote: - On Tue, Feb 08, 2011 at 10:16:02PM -0500, Tom Lane wrote: - - David Kerr <dmk@mr-paradox.net> writes: - - > I'm getting the above error in one of my dev DBs. - - - - Would you poke around in the system catalogs and find where the dangling - - reference is located? Have you got any idea of how to reproduce this - - failure from a standing start? - - - - regards, tom lane - - - - Ok i'll dig around. - - I spoke with the developer, he claims not to have done anything to the DB. so i don't - know how to reproduce the state. (but i do get the error every time i use pg_dump on - the DB). it seems that this DB is mainly used as a target for junit testing. - - Also, i don't know if this is related but I'm seeing quite a few: - Feb 9 17:04:17 db001 postgres-9[21285]: [28-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4223 - Feb 9 17:04:17 db001 postgres-9[21285]: [29-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4224 - Feb 9 17:04:17 db001 postgres-9[21285]: [30-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4225 - Feb 9 17:04:17 db001 postgres-9[21285]: [31-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4226 - Feb 9 17:04:17 db001 postgres-9[21285]: [32-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4227 - Feb 9 17:04:17 db001 postgres-9[21285]: [33-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4228 - Feb 9 17:04:17 db001 postgres-9[21285]: [34-1] user=,db=,pid=21285,2011-02-09 17:04:17 UTC WARNING: PD_ALL_VISIBLE flagwas incorrectly set in relation "test_event_result_fact" page 4229 - - - It seems like a system process is reporting it, i tried to vacuum all of my DBs to try to - narrow down which DB it's in - didn't generate the error. - - I suppose it's possible that the process is fixing the data and then it gets broken again. - - Any pointers would be helpful! Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes are associated with the wrong (invalid / nonexistant ) schema. However, there are correct entries for those objects as well. So these are definitly just dangling references. When we do a data "refresh" here, i do a drop <schema> cascade; in the DB and then pg_restore -Fc --disable-triggers the datasets are made by pg_dump -Fc --disable-triggers -s -n <schema> The only thing strange that may come up is that the dumps were created by 8.3. (also, i've got multiple developers all with their own DB and this is the only one with this particular problem) The 'PD_ALL_VISIBLE' error above seems to not be related to this particular problem. (although it could still be in this database). Dave
David Kerr <dmk@mr-paradox.net> writes:
> Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes are associated
> with the wrong (invalid / nonexistant ) schema.
> However, there are correct entries for those objects as well. So these are definitly just dangling references.
Please be more specific. What are the bad entries exactly (what values,
in which columns of what catalogs) and what do you mean by "there are
correct entries as well"?
> When we do a data "refresh" here, i do a drop <schema> cascade; in the DB and then
> pg_restore -Fc --disable-triggers
Hm. We have seen occasional reports of drop cascade failing to delete
all the dependent objects, but it's pretty hard to see how that could
happen ...
regards, tom lane
On Wed, Feb 09, 2011 at 02:15:06PM -0500, Tom Lane wrote: - David Kerr <dmk@mr-paradox.net> writes: - > Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes are associated - > with the wrong (invalid / nonexistant ) schema. - - > However, there are correct entries for those objects as well. So these are definitly just dangling references. - - Please be more specific. What are the bad entries exactly (what values, - in which columns of what catalogs) and what do you mean by "there are - correct entries as well"? pg_class has 5 relname entries associated with a relnamespace (58698) that doesn't exist in pg_namespace. Those relname entries corrispond to 2 tables and their PKs and a sequence (for the PK of one of the tables). Those objects do have valid entries in pg_class as well (they point to a different relnamespace) Is that more clear? - > When we do a data "refresh" here, i do a drop <schema> cascade; in the DB and then - > pg_restore -Fc --disable-triggers - - Hm. We have seen occasional reports of drop cascade failing to delete - all the dependent objects, but it's pretty hard to see how that could - happen ... I agree! Dave
On 02/09/2011 11:23 AM, David Kerr wrote: > On Wed, Feb 09, 2011 at 02:15:06PM -0500, Tom Lane wrote: > - David Kerr<dmk@mr-paradox.net> writes: > -> Ok, I found the bad entries, 2 tables a sequence and 2 primary key indexes are associated > -> with the wrong (invalid / nonexistant ) schema. > - > -> However, there are correct entries for those objects as well. So these are definitly just dangling references. > - > - Please be more specific. What are the bad entries exactly (what values, > - in which columns of what catalogs) and what do you mean by "there are > - correct entries as well"? > > pg_class has 5 relname entries associated with a relnamespace (58698) that doesn't exist in pg_namespace. > > Those relname entries corrispond to 2 tables and their PKs and a sequence (for the PK of one of the tables). > > Those objects do have valid entries in pg_class as well (they point to a different relnamespace) > > Is that more clear? So i removed the 5 entries from pg_class, but i still get that error when trying to pg_dump: pg_dump: schema with OID 58698 does not exist Any other ideas where i could look? thanks Dave
David Kerr <dmk@mr-paradox.net> writes:
> So i removed the 5 entries from pg_class, but i still get that error
> when trying to pg_dump:
> pg_dump: schema with OID 58698 does not exist
> Any other ideas where i could look?
Well, it could be any catalog with a namespace column. But I'm
wondering about pg_depend links from those tables you removed to
the schema.
regards, tom lane
On Fri, Feb 11, 2011 at 03:17:51PM -0500, Tom Lane wrote: - David Kerr <dmk@mr-paradox.net> writes: - > So i removed the 5 entries from pg_class, but i still get that error - > when trying to pg_dump: - - > pg_dump: schema with OID 58698 does not exist - - > Any other ideas where i could look? - - Well, it could be any catalog with a namespace column. But I'm - wondering about pg_depend links from those tables you removed to - the schema. I'm not sure if it's possible for me to follow the link to pg_depend after i deleted objects so I guess i'll have to try that next time if this happens again. The other references to that schema were in pg_type and pg_constraint. After those were removed I was able to run pg_dump on it. thanks Dave