pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedall views in database broken at once
9+ messages / 4 participants
[nested] [flat]
* all views in database broken at once
@ 2001-03-24 16:16 Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Andrew Perrin @ 2001-03-24 16:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: all views in database broken at once
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
@ 2001-03-24 17:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:31 ` Re: all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2001-03-24 17:06 UTC (permalink / raw)
To: Andrew Perrin <aperrin@socrates.berkeley.edu>; +Cc: 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 :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...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: all views in database broken at once
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-25 00:31 ` Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-25 00:50 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Andrew Perrin @ 2001-03-25 00:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Perrin <aperrin@socrates.Berkeley.EDU>; 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 :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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: all views in database broken at once
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:31 ` Re: all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
@ 2001-03-25 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:57 ` Re: all views in database broken at once Mathijs Brands <mathijs@ilse.nl>
2001-03-25 12:57 ` Re: all views in database broken at once Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2001-03-25 00:50 UTC (permalink / raw)
To: Andrew Perrin <aperrin@socrates.berkeley.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: all views in database broken at once
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:31 ` Re: all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-25 00:50 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-25 00:57 ` Mathijs Brands <mathijs@ilse.nl>
2001-03-25 04:36 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Mathijs Brands @ 2001-03-25 00:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 books
which are your very own".
Sir Arthur Conan Doyle (1859-1930)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: all views in database broken at once
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:31 ` Re: all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-25 00:50 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:57 ` Re: all views in database broken at once Mathijs Brands <mathijs@ilse.nl>
@ 2001-03-25 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 17:14 ` Re: all views in database broken at once Mathijs Brands <mathijs@ilse.nl>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2001-03-25 04:36 UTC (permalink / raw)
To: Mathijs Brands <mathijs@ilse.nl>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: all views in database broken at once
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:31 ` Re: all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-25 00:50 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:57 ` Re: all views in database broken at once Mathijs Brands <mathijs@ilse.nl>
2001-03-25 04:36 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-25 17:14 ` Mathijs Brands <mathijs@ilse.nl>
2001-03-27 09:43 ` Re: all views in database broken at once Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Mathijs Brands @ 2001-03-25 17:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 the
symmetry of shelves, and creators of odd volumes."
Charles Lamb (1775-1834)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: all views in database broken at once
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:31 ` Re: all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-25 00:50 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:57 ` Re: all views in database broken at once Mathijs Brands <mathijs@ilse.nl>
2001-03-25 04:36 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 17:14 ` Re: all views in database broken at once Mathijs Brands <mathijs@ilse.nl>
@ 2001-03-27 09:43 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jan Wieck @ 2001-03-27 09:43 UTC (permalink / raw)
To: Mathijs Brands <mathijs@ilse.nl>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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 attributes to hold
just the backparsed rule qualification and actions
(backparsed from the nodes like done for 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: all views in database broken at once
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:31 ` Re: all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-25 00:50 ` Re: all views in database broken at once Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-25 12:57 ` Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 9+ messages in thread
From: Jan Wieck @ 2001-03-25 12:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Perrin <aperrin@socrates.berkeley.edu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2001-03-27 09:43 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-24 16:16 all views in database broken at once Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-24 17:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:31 ` Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-25 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 00:57 ` Mathijs Brands <mathijs@ilse.nl>
2001-03-25 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-25 17:14 ` Mathijs Brands <mathijs@ilse.nl>
2001-03-27 09:43 ` Jan Wieck <JanWieck@Yahoo.com>
2001-03-25 12:57 ` Jan Wieck <JanWieck@Yahoo.com>
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