Thread: all views in database broken at once
Greetings- I'm in a bit of a pickle. I rebuilt a big query on top of which lots of little queries rest, so as to use some new columns in the query. Now, I get error messages when trying to access any view that SELECTs from the rebuilt query: 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. Thanks for any advice. ---------------------------------------------------------------------- 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
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...
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 >
Andrew Perrin <aperrin@socrates.berkeley.edu> writes:
> But I'm intrigued: what is it that causes this? Is it *my*
> recreating the view on which the other views depend,
Yes. You dropped and recreated the view --- the new version may have
the same name but it's not the same OID, so it isn't the same object.
And the other views refer to it by OID.
The ultimate solution should have two parts, IMHO:
1. Dependency checking so that you *can't* drop a view that is still
referenced. However this will not be complete --- it's not clear that
we can detect references inside PL functions, for example.
2. An ALTER VIEW command that lets you change a view's defining query,
while keeping the same OID, as long as the names and types of the output
columns don't change. This would reduce the need to drop and recreate
views.
regards, tom lane
On Sat, Mar 24, 2001 at 07:50:00PM -0500, Tom Lane allegedly wrote: > Andrew Perrin <aperrin@socrates.berkeley.edu> writes: > > But I'm intrigued: what is it that causes this? Is it *my* > > recreating the view on which the other views depend, > > Yes. You dropped and recreated the view --- the new version may have > the same name but it's not the same OID, so it isn't the same object. > And the other views refer to it by OID. > > The ultimate solution should have two parts, IMHO: > > 1. Dependency checking so that you *can't* drop a view that is still > referenced. However this will not be complete --- it's not clear that > we can detect references inside PL functions, for example. > > 2. An ALTER VIEW command that lets you change a view's defining query, > while keeping the same OID, as long as the names and types of the output > columns don't change. This would reduce the need to drop and recreate > views. How about being able to recompile them (keeping the SQL around in the system catalogs)? Doesn't Oracle allow you to do something like that? Mathijs -- "It is a great thing to start life with a small number of really good bookswhich are your very own". Sir Arthur ConanDoyle (1859-1930)
Mathijs Brands <mathijs@ilse.nl> writes:
> How about being able to recompile them (keeping the SQL around in the
> system catalogs)? Doesn't Oracle allow you to do something like that?
That's another possibility. It's not real clear that there's any
advantage to storing rules in preparsed form to begin with --- if
we just stored the original text and reparsed it each time it was
read, the system would be vastly more flexible, and probably not
noticeably slower.
Right now the parser doesn't really support this: there's no way to
extract the portion of the original query string that corresponds
to the body of the CREATE RULE/VIEW command. But that could be fixed
with some straightforward hacking...
regards, tom lane
Tom Lane wrote: > Andrew Perrin <aperrin@socrates.berkeley.edu> writes: > > But I'm intrigued: what is it that causes this? Is it *my* > > recreating the view on which the other views depend, > > Yes. You dropped and recreated the view --- the new version may have > the same name but it's not the same OID, so it isn't the same object. > And the other views refer to it by OID. > > The ultimate solution should have two parts, IMHO: > > 1. Dependency checking so that you *can't* drop a view that is still > referenced. However this will not be complete --- it's not clear that > we can detect references inside PL functions, for example. PL's aren't too critical. A simple reconnect will whipe out all SPI plans in PL functions and RI triggers, so they'll notice the changes. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Sat, Mar 24, 2001 at 11:36:56PM -0500, Tom Lane allegedly wrote: > Mathijs Brands <mathijs@ilse.nl> writes: > > How about being able to recompile them (keeping the SQL around in the > > system catalogs)? Doesn't Oracle allow you to do something like that? > > That's another possibility. It's not real clear that there's any > advantage to storing rules in preparsed form to begin with --- if > we just stored the original text and reparsed it each time it was > read, the system would be vastly more flexible, and probably not > noticeably slower. But every bit of performance counts, of course... Mathijs -- "Borrowers of books -- those mutilators of collections, spoilers of thesymmetry of shelves, and creators of odd volumes." Charles Lamb (1775-1834)
Mathijs Brands wrote: > On Sat, Mar 24, 2001 at 11:36:56PM -0500, Tom Lane allegedly wrote: > > Mathijs Brands <mathijs@ilse.nl> writes: > > > How about being able to recompile them (keeping the SQL around in the > > > system catalogs)? Doesn't Oracle allow you to do something like that? > > > > That's another possibility. It's not real clear that there's any > > advantage to storing rules in preparsed form to begin with --- if > > we just stored the original text and reparsed it each time it was > > read, the system would be vastly more flexible, and probably not > > noticeably slower. > > But every bit of performance counts, of course... The question is if parsing the original query is that more expensive than converting the printed node tree back into it's binary representation, what's done now. And then again, this is only done when the relation is opened and it's RelationData not found in the relcache. If the relcache serves well, this happens once per connection. OTOH, due to toast we don't need to save space in the pg_rewrite tuples any more. Adding two new attributesto hold just the backparsed rule qualification and actions (backparsed from the nodes like donefor pg_dump), it might be easy to create a utility that recompiles rules - for one or all relations. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com