Re: pg_dump: schema with OID 58698 does not exist

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