pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Perrin <aperrin@socrates.berkeley.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: all views in database broken at once
Date: Sat, 24 Mar 2001 12:06:41 -0500
Message-ID: <23099.985453601@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.21.0103241113240.26693-100000@nujoma.perrins>
References: <Pine.LNX.4.21.0103241113240.26693-100000@nujoma.perrins>

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



view thread (9+ messages)  latest in thread

Message-ID: <23099.985453601@sss.pgh.pa.us>
Permalink:  ../23099.985453601@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23099.985453601@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, aperrin@socrates.berkeley.edu
  Subject: Re: all views in database broken at once
  In-Reply-To: <23099.985453601@sss.pgh.pa.us>
  References: <Pine.LNX.4.21.0103241113240.26693-100000@nujoma.perrins> <23099.985453601@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox