Обсуждение: unusual "relation xxx does not exist" problem
I'm trying to migrate a database cluster from one server to another. So I did a pg_dumpall, ported everything over, yada yada yada. I have one particular schema in one particular database that is giving me trouble. All names for tables, sequences, functions, etc. are lowercase. I repeat: everything is lowercase. I'm using pgadmin iii to look at everything and I continually get "relation not found" errors with my "failurelog" table and other tables. I've tried DROP CASCADEing the schema and database and then rebuilding one table at a time, but it just is not working. I've also done another experiment where I create a brand new table that never existed. I still get "relation not found" errors on that table. The only common thread seems to be the schema. The name of the schema is bar "bar" is not a reserved word in PostgreSQL, according to the documentation. Is there any corrupted information that might be stored in the templates (template0 template1) that would account for this problem? Could it be a bug in pgadmin? Bill Eaton Southern California
On Wed, Jun 28, 2006 at 09:02:04AM -0700, Bill Eaton wrote: > I'm trying to migrate a database cluster from one server to another. So I > did a pg_dumpall, ported everything over, yada yada yada. > > I have one particular schema in one particular database that is giving me > trouble. All names for tables, sequences, functions, etc. are lowercase. I > repeat: everything is lowercase. > > I'm using pgadmin iii to look at everything and I continually get "relation > not found" errors with my "failurelog" table and other tables. I've tried > DROP CASCADEing the schema and database and then rebuilding one table at a > time, but it just is not working. > > I've also done another experiment where I create a brand new table that > never existed. I still get "relation not found" errors on that table. The > only common thread seems to be the schema. > > The name of the schema is > bar > > "bar" is not a reserved word in PostgreSQL, according to the documentation. > Is there any corrupted information that might be stored in the templates > (template0 template1) that would account for this problem? Could it be a bug > in pgadmin? Possibly. Can you reproduce it in psql? Keep in mind you'll need to either specify the schema name or ensure that bar is in search_path. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> > I'm trying to migrate a database cluster from one server to > another. So I > > did a pg_dumpall, ported everything over, yada yada yada. > > > > I have one particular schema in one particular database that is > giving me > > trouble. All names for tables, sequences, functions, etc. are > lowercase. I > > repeat: everything is lowercase. > > > > I'm using pgadmin iii to look at everything and I continually > get "relation > > not found" errors with my "failurelog" table and other tables. > I've tried > > DROP CASCADEing the schema and database and then rebuilding one > table at a > > time, but it just is not working. > > > > I've also done another experiment where I create a brand new table that > > never existed. I still get "relation not found" errors on that > table. The > > only common thread seems to be the schema. > > > > The name of the schema is > > bar > > > > "bar" is not a reserved word in PostgreSQL, according to the > documentation. > > Is there any corrupted information that might be stored in the templates > > (template0 template1) that would account for this problem? > Could it be a bug > > in pgadmin? > > Possibly. Can you reproduce it in psql? Keep in mind you'll need to > either specify the schema name or ensure that bar is in search_path. Oops. That's a detail I left out. I can only reproduce this problem so far in PGAdmin. I couldn't reproduce the error in psql or in a linked table in MSAccess. This is what leads me to wonder if a system table is screwed up. PGAdmin must be looking at system tables behind the scenes to figure out all of the info on tables and field names, etc. Since dropping the schema, the database, and even the "postgres" maintenance database doesn't fix the problem, I was wondering if something could be frelled up in template0 or template1. And I'm scared to mess with those. Though if there is a safe way to regenerate those, I'm perfectly willing to nuke them, too. -Bill Eaton
"Bill Eaton" <ee2@aeroantenna.com> writes:
> Oops. That's a detail I left out. I can only reproduce this problem so far
> in PGAdmin. I couldn't reproduce the error in psql or in a linked table in
> MSAccess.
Turn on query logging so you can see exactly what query PGAdmin is
issuing to provoke the error. There isn't any magic in PGAdmin, it
has to issue the same queries as anyone else.
regards, tom lane
> > Oops. That's a detail I left out. I can only reproduce this > > problem so far > > in PGAdmin. I couldn't reproduce the error in psql or in a > > linked table in > > MSAccess. > > Turn on query logging so you can see exactly what query PGAdmin is > issuing to provoke the error. There isn't any magic in PGAdmin, it > has to issue the same queries as anyone else. > > regards, tom lane Tom, you're a genius. There was some strangeness with the search path for the user I was connected as. The displayed search path was "bar,beam,truss" but the search path should really have been bar, beam, truss Once I changed that and reconnected to the database, everything went back to what I expected. I couldn't have sorted it all out without the logging, which I have since turned off. -Bill Eaton