Re: all views in database broken at once - Mailing list pgsql-sql

Thanks - I appreciate the quick reply. As it turns out, I was able to find
the original SQL I used to generate (most of) the queries, so I'm
okay. But I'm intrigued: what is it that causes this? Is it *my*
recreating the view on which the other views depend, or is it some
internal glitch?

Thanks again.

----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
andrew_perrin@unc.edu - http://www.unc.edu/~aperrin

On Sat, 24 Mar 2001, Tom Lane wrote:

> Andrew Perrin <aperrin@socrates.berkeley.edu> writes:
> > fgdata=# \d sx_l_m_r_a
> > ERROR:  cache lookup of attribute 197 in relation 47074 failed
> > fgdata=# select * from pg_views;
> > ERROR:  cache lookup of attribute 317 in relation 48494 failed
> 
> > A SELECT from the rebuilt query itself works fine, so I know it's not
> > actually a data problem. Is there anything I can do to rebuild these
> > views? I don't think I have the original SQL sitting around to drop and
> > recreate them.
> 
> You're in deep trouble :-(.
> 
> It's at least theoretically possible to fix this by hand, but it'll be
> tedious.  You'll need to dump out the "compiled" form of the view rule
> for each broken view, manually correct the OID for each referenced view,
> and UPDATE pg_rewrite with the corrected rule string.
> 
> A quick example:
> 
> regression=# create view vv1 as select * from int8_tbl;
> CREATE
> regression=# select ev_action from pg_rewrite where rulename = '_RETvv1';
> 
> ({ QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs
false:hasSubLinks false :rtable ({ RTE :relname vv1 :relid 147764  :subquery <> :alias { ATTR :relname *OLD* :attrs <>}
:eref{ ATTR :relname *OLD* :attrs ( "q1"   "q2" )} :inh false :inFromCl false :checkForRead false :checkForWrite false
:checkAsUser0} { RTE :relname vv1 :relid 147764  :subquery <> :alias { ATTR :relname *NEW* :attrs <>} :eref { ATTR
:relname*NEW* :attrs ( "q1"   "q2" )} :inh false :inFromCl false :checkForRead false :checkForWrite false :checkAsUser
0}{ RTE :relname int8_tbl :relid 18887  :subquery <> :alias <> :eref { ATTR :relname int8_tbl :attrs ( "q1"   "q2" )}
:inhtrue :inFromCl true :checkForRead true :checkForWrite false :checkAsUser 256}) :jointree { FROMEXPR :fromlist ({
RANGETBLREF3 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 20 :restypmod -1
:resnameq1 :reskey 0 :reskeyop 0 :res!
 
so!
> rtgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1 :vartype 20 :vartypmod -1  :varlevelsup 0 :varnoold 3
:varoattno1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 20 :restypmod -1 :resname q2 :reskey 0 :reskeyop 0
:ressortgroupref0 :resjunk false } :expr { VAR :varno 3 :varattno 2 :vartype 20 :vartypmod -1  :varlevelsup 0 :varnoold
3:varoattno 2}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <>
:setOperations<> :resultRelations ()})
 
> 
> What you need to fix are the :relid fields of the RTE entries for the
> referenced tables.  The :relname field of the RTE gives the real name
> of the table it references, and you look in pg_class for the associated
> OID.  For example,
> 
> regression=# select oid from pg_class where relname = 'int8_tbl';
>   oid
> -------
>  18887
> (1 row)
> 
> shows that the above view's reference to int8_tbl isn't broken.
> 
> Of course you'll need to be superuser to do the UPDATE on pg_rewrite,
> and you will probably find that you need to quit and restart the backend
> before it will use the changed view definition.
> 
> Good luck!
> 
>             regards, tom lane
> 
> PS: Yes, I know we gotta fix this...
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
>