pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
10+ messages / 4 participants
[nested] [flat]
* problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
@ 2007-03-09 15:40 Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Glen W. Mabey @ 2007-03-09 15:40 UTC (permalink / raw)
To: pgsql-admin
Hello,
After upgrading from 8.1.6 to 8.1.8 (using Debian testing packages) I
started to get a really strange error, and I have not been able to gain
any insights into this from the list archives.
When I SELECT any records from a certain table ("Acquisitions"), I get:
acqlibdb=> select * from "Acquisitions";
ERROR: relation "Acquisitions" does not exist
acqlibdb=> select * from public."Acquisitions";
ERROR: relation "public.Acquisitions" does not exist
However, a View that is based on that table still works:
acqlibdb=> select * from "ViewAcquisitions" LIMIT 2;
and
acqlibdb=> select * from pg_tables;
shows "Acquisitions" as the final result.
Any ideas????? I'm desparate here.
Thanks,
Glen
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
@ 2007-03-09 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2007-03-09 16:41 UTC (permalink / raw)
To: Glen W. Mabey <Glen.Mabey@swri.org>; +Cc: pgsql-admin
"Glen W. Mabey" <Glen.Mabey@swri.org> writes:
> When I SELECT any records from a certain table ("Acquisitions"), I get:
> acqlibdb=> select * from "Acquisitions";
> ERROR: relation "Acquisitions" does not exist
> acqlibdb=> select * from public."Acquisitions";
> ERROR: relation "public.Acquisitions" does not exist
Maybe it's not in the public schema but some other one (implying you
forgot about a nondefault search_path setting).
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-09 17:01 ` Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 19:11 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 20:20 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Igor Neyman <ineyman@perceptron.com>
0 siblings, 2 replies; 10+ messages in thread
From: Glen W. Mabey @ 2007-03-09 17:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On Fri, Mar 09, 2007 at 11:41:23AM -0500, Tom Lane wrote:
> "Glen W. Mabey" <Glen.Mabey@swri.org> writes:
> > When I SELECT any records from a certain table ("Acquisitions"), I get:
>
> > acqlibdb=> select * from "Acquisitions";
> > ERROR: relation "Acquisitions" does not exist
> > acqlibdb=> select * from public."Acquisitions";
> > ERROR: relation "public.Acquisitions" does not exist
>
> Maybe it's not in the public schema but some other one (implying you
> forgot about a nondefault search_path setting).
Humm. I don't think so, since I've never altered the search_path
settings, nor used any schema besides public. And I've been working
with this database for 8 months now without ever encountering such an
error. And all of the other tables work just fine.
Is there some way to force an integrity check of the entire database?
Thank you for your reply.
Glen
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
@ 2007-03-09 19:11 ` Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 20:51 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Greg Sabino Mullane <greg@turnstep.com>
1 sibling, 1 reply; 10+ messages in thread
From: Glen W. Mabey @ 2007-03-09 19:11 UTC (permalink / raw)
To: pgsql-admin
On Fri, Mar 09, 2007 at 11:01:08AM -0600, Glen W. Mabey wrote:
> On Fri, Mar 09, 2007 at 11:41:23AM -0500, Tom Lane wrote:
> > "Glen W. Mabey" <Glen.Mabey@swri.org> writes:
> > > When I SELECT any records from a certain table ("Acquisitions"), I get:
> >
> > > acqlibdb=> select * from "Acquisitions";
> > > ERROR: relation "Acquisitions" does not exist
> > > acqlibdb=> select * from public."Acquisitions";
> > > ERROR: relation "public.Acquisitions" does not exist
> >
> > Maybe it's not in the public schema but some other one (implying you
> > forgot about a nondefault search_path setting).
>
> Humm. I don't think so, since I've never altered the search_path
> settings, nor used any schema besides public. And I've been working
> with this database for 8 months now without ever encountering such an
> error. And all of the other tables work just fine.
Is there a more appropriate list to submit this question to?
Is it possible that this is a bug?
How could I determine whether it is?
Thanks,
Glen
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 19:11 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
@ 2007-03-09 20:51 ` Greg Sabino Mullane <greg@turnstep.com>
2007-03-09 20:56 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
0 siblings, 1 reply; 10+ messages in thread
From: Greg Sabino Mullane @ 2007-03-09 20:51 UTC (permalink / raw)
To: pgsql-admin
Hash: RIPEMD160
> > > When I SELECT any records from a certain table ("Acquisitions"), I get:
> >
> > > acqlibdb=> select * from "Acquisitions";
> > > ERROR: relation "Acquisitions" does not exist
..>
> Humm. I don't think so, since I've never altered the search_path
> settings, nor used any schema besides public. And I've been working
> with this database for 8 months now without ever encountering such an
> error. And all of the other tables work just fine.
See what this gives you:
SELECT nspname, relname, relkind
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid
AND lower(relname) ~ 'acqu'
ORDER BY 2,1,3;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200703091547
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFF8cfuvJuQZxSWSsgRA0HYAJ0dWE1L3Ewzu8D8pWyJZd8vniw2zgCfaqYZ
zCX9tS5iBecoDP7nn5A7Jy0=
=0XFc
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 19:11 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 20:51 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Greg Sabino Mullane <greg@turnstep.com>
@ 2007-03-09 20:56 ` Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 23:29 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Glen W. Mabey @ 2007-03-09 20:56 UTC (permalink / raw)
To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-admin
On Fri, Mar 09, 2007 at 08:51:44PM +0000, Greg Sabino Mullane wrote:
>
> Hash: RIPEMD160
>
>
> > > > When I SELECT any records from a certain table ("Acquisitions"), I get:
> > >
> > > > acqlibdb=> select * from "Acquisitions";
> > > > ERROR: relation "Acquisitions" does not exist
> ..>
> > Humm. I don't think so, since I've never altered the search_path
> > settings, nor used any schema besides public. And I've been working
> > with this database for 8 months now without ever encountering such an
> > error. And all of the other tables work just fine.
>
> See what this gives you:
>
> SELECT nspname, relname, relkind
> FROM pg_class c, pg_namespace n
> WHERE c.relnamespace = n.oid
> AND lower(relname) ~ 'acqu'
> ORDER BY 2,1,3;
I have no idea what that does, but here is the output:
nspname | relname | relkind
---------+------------------------------+---------
public | Acquisition_pkey | i
public | Acquisitions | r
public | Acquisitions_id_seq | S
public | Acquisitions_location_id_key | i
public | ViewAcquisitions | v
public | viewacquisitionsnew | v
What I see is a bunch of stuff related to the Acquisitions table,
including a relation for the primary key, which was created back when
the table was named "Acquisition" (not plural), and two different views
which draw on Acquisitions, both of which appear to be fully
functional.
Thanks for you reply --
Glen
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 19:11 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 20:51 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Greg Sabino Mullane <greg@turnstep.com>
2007-03-09 20:56 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
@ 2007-03-09 23:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-12 15:03 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2007-03-09 23:29 UTC (permalink / raw)
To: Glen W. Mabey <Glen.Mabey@swri.org>; +Cc: Greg Sabino Mullane <greg@turnstep.com>; pgsql-admin
"Glen W. Mabey" <Glen.Mabey@swri.org> writes:
> What I see is a bunch of stuff related to the Acquisitions table,
> including a relation for the primary key, which was created back when
> the table was named "Acquisition" (not plural), and two different views
> which draw on Acquisitions, both of which appear to be fully
> functional.
I wonder whether "reindex index pg_class_relname_nsp_index" would make
this go away.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 19:11 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 20:51 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Greg Sabino Mullane <greg@turnstep.com>
2007-03-09 20:56 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 23:29 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-12 15:03 ` Glen W. Mabey <Glen.Mabey@swri.org>
0 siblings, 0 replies; 10+ messages in thread
From: Glen W. Mabey @ 2007-03-12 15:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Sabino Mullane <greg@turnstep.com>; pgsql-admin
On Fri, Mar 09, 2007 at 06:29:21PM -0500, Tom Lane wrote:
> "Glen W. Mabey" <Glen.Mabey@swri.org> writes:
> > What I see is a bunch of stuff related to the Acquisitions table,
> > including a relation for the primary key, which was created back when
> > the table was named "Acquisition" (not plural), and two different views
> > which draw on Acquisitions, both of which appear to be fully
> > functional.
>
> I wonder whether "reindex index pg_class_relname_nsp_index" would make
> this go away.
Great -- that did it.
However, after I executed that command, SELECT FROM "Acquisitions" LIMIT 1; still
indicated that "Acquisitions" did not exist. But then a pg_dump command
succeeded, and afterwared, SELECT FROM "Acquisitions" LIMIT 1; did also
succeed.
Thank you very much for your help.
Is this a bug that should be reported?
And, what does "reindex index pg_class_relname_nsp_index" do, anyway?
Also, there is another user who has posted to this list describing what
I first saw when encountering this problem: trouble running pg_dump.
Thanks,
Glen
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
@ 2007-03-09 20:20 ` Igor Neyman <ineyman@perceptron.com>
2007-03-09 20:34 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
1 sibling, 1 reply; 10+ messages in thread
From: Igor Neyman @ 2007-03-09 20:20 UTC (permalink / raw)
To: Glen W. Mabey <Glen.Mabey@swri.org>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Why wouldn't you look at the definition of the view which is based on
this table (and which you claim is still working), and how it references
basic table?
M.b. it'll point you to the actual table location.
Igor
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Glen W. Mabey
Sent: Friday, March 09, 2007 12:01 PM
To: Tom Lane
Cc: PostgreSQL Admin Mailing List
Subject: Re: [ADMIN] problem upgrading from 8.1.6 to 8.1.8 --- relation
<tablename> does not exist
On Fri, Mar 09, 2007 at 11:41:23AM -0500, Tom Lane wrote:
> "Glen W. Mabey" <Glen.Mabey@swri.org> writes:
> > When I SELECT any records from a certain table ("Acquisitions"), I
get:
>
> > acqlibdb=> select * from "Acquisitions";
> > ERROR: relation "Acquisitions" does not exist
> > acqlibdb=> select * from public."Acquisitions";
> > ERROR: relation "public.Acquisitions" does not exist
>
> Maybe it's not in the public schema but some other one (implying you
> forgot about a nondefault search_path setting).
Humm. I don't think so, since I've never altered the search_path
settings, nor used any schema besides public. And I've been working
with this database for 8 months now without ever encountering such an
error. And all of the other tables work just fine.
Is there some way to force an integrity check of the entire database?
Thank you for your reply.
Glen
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 20:20 ` Re: problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Igor Neyman <ineyman@perceptron.com>
@ 2007-03-09 20:34 ` Glen W. Mabey <Glen.Mabey@swri.org>
0 siblings, 0 replies; 10+ messages in thread
From: Glen W. Mabey @ 2007-03-09 20:34 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
The definition of the View refers to just "Acquisitions".
Glen
On Fri, Mar 09, 2007 at 03:20:51PM -0500, Igor Neyman wrote:
> Why wouldn't you look at the definition of the view which is based on
> this table (and which you claim is still working), and how it references
> basic table?
> M.b. it'll point you to the actual table location.
>
> Igor
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Glen W. Mabey
> Sent: Friday, March 09, 2007 12:01 PM
> To: Tom Lane
> Cc: PostgreSQL Admin Mailing List
> Subject: Re: [ADMIN] problem upgrading from 8.1.6 to 8.1.8 --- relation
> <tablename> does not exist
>
> On Fri, Mar 09, 2007 at 11:41:23AM -0500, Tom Lane wrote:
> > "Glen W. Mabey" <Glen.Mabey@swri.org> writes:
> > > When I SELECT any records from a certain table ("Acquisitions"), I
> get:
> >
> > > acqlibdb=> select * from "Acquisitions";
> > > ERROR: relation "Acquisitions" does not exist
> > > acqlibdb=> select * from public."Acquisitions";
> > > ERROR: relation "public.Acquisitions" does not exist
> >
> > Maybe it's not in the public schema but some other one (implying you
> > forgot about a nondefault search_path setting).
>
> Humm. I don't think so, since I've never altered the search_path
> settings, nor used any schema besides public. And I've been working
> with this database for 8 months now without ever encountering such an
> error. And all of the other tables work just fine.
>
> Is there some way to force an integrity check of the entire database?
>
> Thank you for your reply.
>
> Glen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-03-12 15:03 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-09 15:40 problem upgrading from 8.1.6 to 8.1.8 --- relation <tablename> does not exist Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 17:01 ` Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 19:11 ` Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 20:51 ` Greg Sabino Mullane <greg@turnstep.com>
2007-03-09 20:56 ` Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 23:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-12 15:03 ` Glen W. Mabey <Glen.Mabey@swri.org>
2007-03-09 20:20 ` Igor Neyman <ineyman@perceptron.com>
2007-03-09 20:34 ` Glen W. Mabey <Glen.Mabey@swri.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox