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

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 :resso!
 
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...