pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
all 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>
  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 17:06  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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-25 00:31  Andrew Perrin <aperrin@socrates.berkeley.edu>
  parent: 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-25 00:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Andrew Perrin <aperrin@socrates.berkeley.edu>
  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-25 00:57  Mathijs Brands <mathijs@ilse.nl>
  parent: 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-25 04:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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-25 12:57  Jan Wieck <JanWieck@Yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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

* Re: all views in database broken at once
@ 2001-03-25 17:14  Mathijs Brands <mathijs@ilse.nl>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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-27 09:43  Jan Wieck <JanWieck@Yahoo.com>
  parent: Mathijs Brands <mathijs@ilse.nl>
  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


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