Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_views view doesn't work

pg_views view doesn't work

From
"Jim Buttafuoco"
Date:
Hi all,

I am having a problem with the "pg_views" view.  It seems that the
internal function pg_get_viewdef() is the problem.  See below.

This problem is preventing me from dumping the database (either as 1
export or 1 table at a time).  I have recreated the database, but the
problem seems to have come back again last night.  If I create a new
database now the problem doesn't happen.

Thanks
Jim

TEST=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)


TEST=# select * from pg_views;
ERROR:  cache lookup for proc 189295778 failed
TEST=#

TEST=# \d pg_views
       View "pg_views"
 Attribute  | Type | Modifier
------------+------+----------
 viewname   | name |
 viewowner  | name |
 definition | text |
View definition: SELECT c.relname AS viewname,
pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.relname) AS
definition FROM pg_class c WHERE (c.relkind = 'v'::"char");

TEST=# select pg_get_viewdef(c.relname) from pg_class c;
ERROR:  cache lookup for proc 189295778 failed
TEST=#

Re: pg_views view doesn't work

From
Tom Lane
Date:
"Jim Buttafuoco" <jim@spectrumtelecorp.com> writes:
> I am having a problem with the "pg_views" view.  It seems that the
> internal function pg_get_viewdef() is the problem.  See below.

It looks to me like you've got some one view that refers to a
no-longer-existent function.  To figure out which it is, try
"select * from pg_views limit N" for various N until you know
where the failing row is, then extract just the viewname of that
row.

            regards, tom lane

Re: pg_views view doesn't work

From
"Jim Buttafuoco"
Date:
Tom

Thanks for your reply.

I dropped ALL of my views and recreated from a script.  This seems to
have worked.  I am running my nightly jobs now to see if some how they
are causing the views to break.  I will report back later.

Jim




> "Jim Buttafuoco" <jim@spectrumtelecorp.com> writes:
> > I am having a problem with the "pg_views" view.  It seems that the
> > internal function pg_get_viewdef() is the problem.  See below.
>
> It looks to me like you've got some one view that refers to a
> no-longer-existent function.  To figure out which it is, try
> "select * from pg_views limit N" for various N until you know
> where the failing row is, then extract just the viewname of that
> row.
>
>             regards, tom lane
>
>