Обсуждение: problem upgrading from 8.1.6 to 8.1.8 --- relation does not exist

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

"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

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

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

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

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
>

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-----

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

"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

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