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)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin
On Sat, 24 Mar 2001, Tom Lane wrote:
> Andrew Perrin <[EMAIL PROTECTED]> 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 :checkAsUser 0} { 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" )} :inh true
>:inFromCl true :checkForRead true :checkForWrite false :checkAsUser 256}) :jointree {
>FROMEXPR :fromlist ({ RANGETBLREF 3 }) :quals <>} :rowMarks () :targetList ({
>TARGETENTRY :resdom { RESDOM :resno 1 :restype 20 :restypmod -1 :resname q1 :reskey 0
>:reskeyop 0 :res!
so!
> rtgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1 :vartype 20
>:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 1}} { TARGETENTRY :resdom {
>RESDOM :resno 2 :restype 20 :restypmod -1 :resname q2 :reskey 0 :reskeyop 0
>:ressortgroupref 0 :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
>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
