Thread: Rule recompilation

Rule recompilation

From
Jan Wieck
Date:
Hi,
   I'd  like  to  add  another column to pg_rewrite, holding the   string representation of the  rewrite  rule.  A  new
utility   command  will  then  allow  to recreate the rules (internally   DROP/CREATE, but that doesn't matter).
 
   This would be a big help in case anything used in a  view  or   other  rules  get's  dropped  and  recreated (like
underlying  tables). There is of course a difference between the original   CREATE  RULE/VIEW  statement and the string
storedhere. This   is because we cannot rely on the actual query buffer but have   to parseback the parsetree like done
bythe utility functions   used for pg_rules. Thus, changing a column  name  of  a  base   table will break the view
eitherway.
 
   Anyway,  what's  the preferred syntax for triggering the rule   recompilation?  I thought about
       ALTER RULE {rulename|ALL} RECOMPILE;
   Where ALL triggers only those rules where the  user  actually   has RULE access right on a relation.


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



RE: Rule recompilation

From
"Mikheev, Vadim"
Date:
>     Anyway,  what's  the preferred syntax for triggering the rule
>     recompilation?  I thought about
> 
>         ALTER RULE {rulename|ALL} RECOMPILE;
> 
>     Where ALL triggers only those rules where the  user  actually
>     has RULE access right on a relation.

In good world rules (PL functions etc) should be automatically
marked as dirty (ie recompilation required) whenever referenced
objects are changed.

Vadim


RE: Rule recompilation

From
"Mikheev, Vadim"
Date:
> > In good world rules (PL functions etc) should be automatically
> > marked as dirty (ie recompilation required) whenever referenced
> > objects are changed.
> 
>     Yepp,  and  it'd  be possible for rules (just not right now).
>     But we're not in  a  really  good  world,  so  it'll  not  be
>     possible for PL's.

Why is it possible in Oracle' world? -:)

Vadim


Re: Rule recompilation

From
Jan Wieck
Date:
Mikheev, Vadim wrote:
> > > In good world rules (PL functions etc) should be automatically
> > > marked as dirty (ie recompilation required) whenever referenced
> > > objects are changed.
> >
> >     Yepp,  and  it'd  be possible for rules (just not right now).
> >     But we're not in  a  really  good  world,  so  it'll  not  be
> >     possible for PL's.
>
> Why is it possible in Oracle' world? -:)
   Because of there limited features?
   Think  about  a  language like PL/Tcl. At the time you call a   script for execution, you cannot even be sure  that
the Tcl   bytecode  compiler parsed anything, so how will you ever know   the complete set of objects referenced from
thisfunction?
 
   And PL/pgSQL? We don't prepare all the  statements  into  SPI   plans  at  compile  time. We wait until the separate
branches  are needed, so how do you know offhand here?
 
   In the PL/pgSQL case it *might* be possible. But is it  worth   it?


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



Re: Rule recompilation

From
Jan Wieck
Date:
Mikheev, Vadim wrote:
> >     Anyway,  what's  the preferred syntax for triggering the rule
> >     recompilation?  I thought about
> >
> >         ALTER RULE {rulename|ALL} RECOMPILE;
> >
> >     Where ALL triggers only those rules where the  user  actually
> >     has RULE access right on a relation.
>
> In good world rules (PL functions etc) should be automatically
> marked as dirty (ie recompilation required) whenever referenced
> objects are changed.
   Yepp,  and  it'd  be possible for rules (just not right now).   But we're not in  a  really  good  world,  so  it'll
not  be   possible for PL's.
 


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



Re: Rule recompilation

From
Alex Pilosov
Date:
I remember awhile ago, someone floated the idea of a dependency view which
would list all objects and what OIDs they have in their plan. (i.e. what
do they depend on). 

I'm definitely no expert in this, but to me, one possible implementation
would be to enhance outfuncs to provide for creation tracking of all
OIDs used in plan, and allow caller to receive this list and do something
with it. This would actually be very simple, as only _outOidList will need
to be modified...(but then again, I'm known for oversimplifying things :)

Then, we can add  ev_depends/oidvector to pg_rewrite and store the
dependency there, and for stored procedures, add a prodepends/oidvector to
pg_proc.

Then, create union of pg_rewrite and pg_proc to list dependencies.

Then, we would be able to provide warning when an object is dropped:
'The following objects depend on this blah blah', and possibly an action
"alter database fixdepends oid" which would recompile everything that
depends on that oid.

How's this sound?

On Thu, 12 Jul 2001, Jan Wieck wrote:

> Hi,
> 
>     I'd  like  to  add  another column to pg_rewrite, holding the
>     string representation of the  rewrite  rule.  A  new  utility
>     command  will  then  allow  to recreate the rules (internally
>     DROP/CREATE, but that doesn't matter).
> 
>     This would be a big help in case anything used in a  view  or
>     other  rules  get's  dropped  and  recreated (like underlying
>     tables). There is of course a difference between the original
>     CREATE  RULE/VIEW  statement and the string stored here. This
>     is because we cannot rely on the actual query buffer but have
>     to parseback the parsetree like done by the utility functions
>     used for pg_rules. Thus, changing a column  name  of  a  base
>     table will break the view either way.
> 
>     Anyway,  what's  the preferred syntax for triggering the rule
>     recompilation?  I thought about
> 
>         ALTER RULE {rulename|ALL} RECOMPILE;
> 
>     Where ALL triggers only those rules where the  user  actually
>     has RULE access right on a relation.
> 
> 
> 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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
> 





Re: Rule recompilation

From
Alex Pilosov
Date:
On Thu, 12 Jul 2001, Jan Wieck wrote:

> Mikheev, Vadim wrote:
> > > > In good world rules (PL functions etc) should be automatically
> > > > marked as dirty (ie recompilation required) whenever referenced
> > > > objects are changed.
> > >
> > >     Yepp,  and  it'd  be possible for rules (just not right now).
> > >     But we're not in  a  really  good  world,  so  it'll  not  be
> > >     possible for PL's.
> >
> > Why is it possible in Oracle' world? -:)
> 
>     Because of there limited features?
> 
>     Think  about  a  language like PL/Tcl. At the time you call a
>     script for execution, you cannot even be sure  that  the  Tcl
>     bytecode  compiler parsed anything, so how will you ever know
>     the complete set of objects referenced from this function?
>     And PL/pgSQL? We don't prepare all the  statements  into  SPI
>     plans  at  compile  time. We wait until the separate branches
>     are needed, so how do you know offhand here?
If plan hasn't been made (oid has not been referenced), does it really
depend on an object?

>     In the PL/pgSQL case it *might* be possible. But is it  worth
>     it?
It'd be possible in general, as long as pl compilers properly keep track
what their objects depend on in pg_proc. (as in my above email).

-alex 



RE: Rule recompilation

From
"Mikheev, Vadim"
Date:
> > Why is it possible in Oracle' world? -:)
> 
>     Because of there limited features?

And now we limit our additional advanced features -:)

>     Think  about  a  language like PL/Tcl. At the time you call a
>     script for execution, you cannot even be sure  that  the  Tcl
>     bytecode  compiler parsed anything, so how will you ever know
>     the complete set of objects referenced from this function?
> 
>     And PL/pgSQL? We don't prepare all the  statements  into  SPI
>     plans  at  compile  time. We wait until the separate branches
>     are needed, so how do you know offhand here?

At the time of creation function body could be parsed and referenced
objects stored in system table (or function could be marked as dirty
and referenced objects would stored at first compilation and after
each subsequent successful after-dirtied-compilation).
Isn't it possible for PL/_ANY_L_ too?

>     In the PL/pgSQL case it *might* be possible. But is it  worth
>     it?

Sure.

Vadim


Re: Rule recompilation

From
Jan Wieck
Date:
Alex Pilosov wrote:
> I remember awhile ago, someone floated the idea of a dependency view which
> would list all objects and what OIDs they have in their plan. (i.e. what
> do they depend on).
>
> I'm definitely no expert in this, but to me, one possible implementation
> would be to enhance outfuncs to provide for creation tracking of all
> OIDs used in plan, and allow caller to receive this list and do something
> with it. This would actually be very simple, as only _outOidList will need
> to be modified...(but then again, I'm known for oversimplifying things :)
>
> Then, we can add  ev_depends/oidvector to pg_rewrite and store the
> dependency there, and for stored procedures, add a prodepends/oidvector to
> pg_proc.
>
> Then, create union of pg_rewrite and pg_proc to list dependencies.
>
> Then, we would be able to provide warning when an object is dropped:
> 'The following objects depend on this blah blah', and possibly an action
> "alter database fixdepends oid" which would recompile everything that
> depends on that oid.
>
> How's this sound?
   Er - oversimplified :-)
   I  remember  it well, because Bruce is mentioning it every so   often and constantly tries to convince me to start a
project   about a dependency table.  I just think it's better not to do   it for 7.2 (didn't we  wanted  to  have  that
released  THIS   year?).
 
   Anyway,  there's  alot  more  to  look  at.  Functions can be   referenced in views, indexes, operators, aggregates
andmaybe   more  places.  Views/rules  can reference allmost any object.   And this only builds the permanent cross
reference.
   We have to take a look at runtime information, telling  which   prepared/saved  SPI plan uses a particular object
andtrigger   automatic re-prepare for the plan in case.
 
   For most objects, there is no such "recompile" possible -  at   least  not  without  storing  alot more information
thannow.   Create a function and based on that  an  operator.  Then  you   drop  the  function and create another one.
Hmmm,pg_operator   doesn't have the function name and argument  types,  it  only   knows the old functions oid. How do
youfind the new function   from here? So  basically  we'd  need  some  sort  of  pg_dump   snippet  associated  with
everyobject and issue an internal   DROP/CREATE using that string to recompile it.
 


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



Re: Rule recompilation

From
Jan Wieck
Date:
Mikheev, Vadim wrote:
> > > Why is it possible in Oracle' world? -:)
> >
> >     Because of there limited features?
>
> And now we limit our additional advanced features -:)
>
> >     Think  about  a  language like PL/Tcl. At the time you call a
> >     script for execution, you cannot even be sure  that  the  Tcl
> >     bytecode  compiler parsed anything, so how will you ever know
> >     the complete set of objects referenced from this function?
> >
> >     And PL/pgSQL? We don't prepare all the  statements  into  SPI
> >     plans  at  compile  time. We wait until the separate branches
> >     are needed, so how do you know offhand here?
>
> At the time of creation function body could be parsed and referenced
> objects stored in system table (or function could be marked as dirty
> and referenced objects would stored at first compilation and after
> each subsequent successful after-dirtied-compilation).
> Isn't it possible for PL/_ANY_L_ too?
   Nonononono!
   PL/Tcl  is  a very good example for that. To load a function,   basically a "proc" command is executed in a Tcl
interpreter.  But  execution  of  Tcl's  "proc"  command  doesn't cause the   bytecode  compiler  to  kick  in  and
actually  parse   the   procedures  body.  So  until  the  first  actual  call of the   function, the Tcl interpreter
justholds  a  string  for  the   body.   Now  a  procedure  body in Tcl is basically a list of   commands with possible
sublists.On call,  only  the  topmost   level  of this list hierarchy is parsed and compiled, command   per command.
Plusrecursively those sublists, needed for this   invocation.
 
   You  cannot control Tcl's bytecode compiler from the outside.   There's no API for that. And Tcl is  a  dynamic
language. A   function might execute dynamic code found in some user table?
 
   Since we don't save bytecode for PL objects,  these  all  are   IMHO runtime dependencies and most of them could be
solvedif   we fix SPI to deal with it correctly.
 


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



Re: Rule recompilation

From
Tom Lane
Date:
Jan Wieck <JanWieck@yahoo.com> writes:
>     are  you  sure  that  this  doesn't have a severe performance
>     impact?

It's not provable, of course, until we try it ... but I think the
performance impact would be small.  Has anyone complained about the
fact that plpgsql functions are stored as source not precompiled
trees?  Seems like the same tradeoff.

>     When and how often are  these  parsetrees  read?  IIRC  these
>     parsetree strings are interpreted somehow during heap_open().

Currently we load them during relcache load, but that's only because
little work need be expended to make it happen.  My vision of how
this should work is that the relcache would load the source text
right away, but computation of the derived state would only happen
when someone demands it, and then the relcache would cache the result.
Take a look at how the list of indexes for each relation is handled
in current sources --- same principle, we don't scan pg_index until
and unless we have to.
        regards, tom lane


Re: Rule recompilation

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.com> writes:
>     And PL/pgSQL? We don't prepare all the  statements  into  SPI
>     plans  at  compile  time. We wait until the separate branches
>     are needed, so how do you know offhand here?

If we haven't prepared a statement yet, then we don't need to reprepare
it, hmm?  So it'd be sufficient to keep track of a list of all objects
referenced *so far* by each plpgsql function.

Your complaints about pltcl and plperl are irrelevant because they don't
save prepared plans.  For the languages that do save prepared plans, it
seems possible to keep track of a list of all objects that each plan
depends on.  So I think that we should try to do it right, rather than
assuming from the start that we can't.

>     In the PL/pgSQL case it *might* be possible. But is it  worth
>     it?

Yes.  If we're not going to do it right, I think we needn't bother to do
it at all.  "Restart your backend" is just as good an answer, probably
better, than "issue a RECOMPILE against everything affected by whatever
you changed".  If the system can't keep track of that, how likely is it
that the user can?
        regards, tom lane


Re: Rule recompilation

From
Tom Lane
Date:
Jan Wieck <JanWieck@yahoo.com> writes:
>     This isn't  local  recompilation  in  current  backend.  It's
>     recreation  of the pg_rewrite entry for a relation, including
>     propagation.

Where I'd like to go (see my previous mail) is that pg_rewrite,
pg_attrdef, and friends store *only* the source text of rules,
default expressions, etc.  No compiled trees at all in the database.
So there's no need to update the database entries, but there is a
need for something like a shared-cache-invalidation procedure to cause
backends to recompile things that depend on updated relations.
        regards, tom lane


Re: Rule recompilation

From
Jan Wieck
Date:
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     There is of course a difference between the original
> >     CREATE  RULE/VIEW  statement and the string stored here. This
> >     is because we cannot rely on the actual query buffer but have
> >     to parseback the parsetree like done by the utility functions
> >     used for pg_rules.
>
> Did you see my comments about extending the parser to make it possible
> to extract the appropriate part of the query buffer?  This would allow
> us to get rid of the reverse-lister (ruleutils.c) entirely, not to
> mention readfuncs.c (but we'd still want outfuncs.c for debugging, I
> suppose).
   Missed that, but sounds good!

>
> >     Anyway,  what's  the preferred syntax for triggering the rule
> >     recompilation?  I thought about
> >         ALTER RULE {rulename|ALL} RECOMPILE;
> >     Where ALL triggers only those rules where the  user  actually
> >     has RULE access right on a relation.
>
> The proposed definition of ALL seems completely off-base.  If I have
> changed my table foo, which is referenced by a rule attached to
> Joe's table bar, I would like to be able to force recompilation of
> Joe's rule.  If I can't do that, a RECOMPILE command is useless.
> I might as well just restart my backend.
>
> BTW, a RECOMPILE command that affects only the current backend is pretty
> useless anyway.  How are you going to propagate the recompile request to
> other backends?
   Create   a   user   table   (for   testing)   and   save  the   pg_get_ruledef() output of all rules into there.
Thenwrite a   little  PL/pgSQL  function that loops over that table and for   each row does
 
       EXECUTE ''drop rule '' || ...       EXECUTE row.ruledef;
   Break a view by dropping and recreating an underlying  table.   Then  see  what  happens  when  executing the stored
proc...   including what happens in the relcache and other backends.
 
   This isn't  local  recompilation  in  current  backend.  It's   recreation  of the pg_rewrite entry for a relation,
including  propagation.
 


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



Re: Rule recompilation

From
Jan Wieck
Date:
Tom Lane wrote:
> Jan Wieck <JanWieck@yahoo.com> writes:
> >     This isn't  local  recompilation  in  current  backend.  It's
> >     recreation  of the pg_rewrite entry for a relation, including
> >     propagation.
>
> Where I'd like to go (see my previous mail) is that pg_rewrite,
> pg_attrdef, and friends store *only* the source text of rules,
> default expressions, etc.  No compiled trees at all in the database.
> So there's no need to update the database entries, but there is a
> need for something like a shared-cache-invalidation procedure to cause
> backends to recompile things that depend on updated relations.

Hmmm,
   are  you  sure  that  this  doesn't have a severe performance   impact?
   When and how often are  these  parsetrees  read?  IIRC  these   parsetree strings are interpreted somehow during
heap_open().  Now you want to  run  a  flex/bison  plus  tons  of  syscache   lookups  for  operator  and  function
candidatesand possible   casting in this place?
 


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



Re: Rule recompilation

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.com> writes:
>     There is of course a difference between the original
>     CREATE  RULE/VIEW  statement and the string stored here. This
>     is because we cannot rely on the actual query buffer but have
>     to parseback the parsetree like done by the utility functions
>     used for pg_rules.

Did you see my comments about extending the parser to make it possible
to extract the appropriate part of the query buffer?  This would allow
us to get rid of the reverse-lister (ruleutils.c) entirely, not to
mention readfuncs.c (but we'd still want outfuncs.c for debugging, I
suppose).

>     Anyway,  what's  the preferred syntax for triggering the rule
>     recompilation?  I thought about
>         ALTER RULE {rulename|ALL} RECOMPILE;
>     Where ALL triggers only those rules where the  user  actually
>     has RULE access right on a relation.

The proposed definition of ALL seems completely off-base.  If I have
changed my table foo, which is referenced by a rule attached to
Joe's table bar, I would like to be able to force recompilation of
Joe's rule.  If I can't do that, a RECOMPILE command is useless.
I might as well just restart my backend.

BTW, a RECOMPILE command that affects only the current backend is pretty
useless anyway.  How are you going to propagate the recompile request to
other backends?
        regards, tom lane


Re: Rule recompilation

From
Alex Pilosov
Date:
On Thu, 12 Jul 2001, Peter Eisentraut wrote:

> Jan Wieck writes:
> 
> >     For most objects, there is no such "recompile" possible -  at
> >     least  not  without  storing  alot more information than now.
> >     Create a function and based on that  an  operator.  Then  you
> >     drop  the  function and create another one. Hmmm, pg_operator
> >     doesn't have the function name and argument  types,  it  only
> >     knows the old functions oid. How do you find the new function
> >     from here?
> 
> In these cases it'd be a lot simpler (and SQL-comforming) to implement the
> DROP THING ... { RESTRICT | CASCADE } options.  This would probably catch
> most honest user errors more cleanly than trying to automatically
> recompile things that perhaps aren't even meant to fit together any
> longer.
Yes, I absolutely agree, and that's the aim of what I'm suggesting...

-alex



Re: Rule recompilation

From
Jan Wieck
Date:
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
>
> >     In the PL/pgSQL case it *might* be possible. But is it  worth
> >     it?
>
> Yes.  If we're not going to do it right, I think we needn't bother to do
> it at all.  "Restart your backend" is just as good an answer, probably
> better, than "issue a RECOMPILE against everything affected by whatever
> you changed".  If the system can't keep track of that, how likely is it
> that the user can?
   Stop!
   We're talking about two different things here.
   Recompilation  (or  better  fixing  Oid  references in system   catalog entries) is required to correct a system
catalogthat   got  inconsistent due to dropping and recreating a particular   object.
 
   Regeneration of runtime things like saved SPI plans might  be   related  to  that, but it's not exactly the same.
Thatsurely   is corrected  by  restarting  the  backend.  But  you  cannot   correct a broken view with a backend
restart,can you?
 
   And  pardon,  but  PL/Tcl can save SPI plans. At least it had   that capability when I wrote the language handler,
so if  it   cannot any more WHO DID THAT?
 


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



Re: Rule recompilation

From
Tom Lane
Date:
Jan Wieck <JanWieck@yahoo.com> writes:
>     Stop!
>     We're talking about two different things here.

You're right: fixing obsoleted querytrees stored in pg_rewrite and
similar catalogs is not the same thing as invalidating cached
query plans in plpgsql, SPI, etc.

However, we could turn them into the same problem if we rearrange the
catalogs to store only source text.  Then there's no need to update any
permanent state, only a need to cause invalidation of derived state
inside various backends.

Each piece of derived state could (and should IMHO) be tagged with a
list of all the objects it depends on; then an invalidation message for
any of those objects would cause that piece of state to be thrown away
and rebuilt at next use.  Just like the catalog caches ...
        regards, tom lane


Re: Rule recompilation

From
Peter Eisentraut
Date:
Jan Wieck writes:

>     For most objects, there is no such "recompile" possible -  at
>     least  not  without  storing  alot more information than now.
>     Create a function and based on that  an  operator.  Then  you
>     drop  the  function and create another one. Hmmm, pg_operator
>     doesn't have the function name and argument  types,  it  only
>     knows the old functions oid. How do you find the new function
>     from here?

In these cases it'd be a lot simpler (and SQL-comforming) to implement the
DROP THING ... { RESTRICT | CASCADE } options.  This would probably catch
most honest user errors more cleanly than trying to automatically
recompile things that perhaps aren't even meant to fit together any
longer.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter



Re: Rule recompilation

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.com> writes:
>     For most objects, there is no such "recompile" possible -  at
>     least  not  without  storing  alot more information than now.
>     Create a function and based on that  an  operator.  Then  you
>     drop  the  function and create another one. Hmmm, pg_operator
>     doesn't have the function name and argument  types,  it  only
>     knows the old functions oid. How do you find the new function
>     from here?

What new function?  The correct system behavior (as yet unimplemented)
would be to *drop* the operator the instant someone drops the underlying
function.

What is more interesting here is an (also unimplemented, but should
exist) ALTER FUNCTION command that can replace the definition text
of an existing function object.  The link from the operator to the
function then does not change --- but we'd like to cause cached plans,
etc, to be rebuilt if they depend on the old function definition via
the operator.

I think it's wrong to see the problem as relinking primary definitions
to point at new objects.  The primary definition of an object does not
need to change, what we need is to be able to update derived data.
pg_rewrite is currently broken in the sense that it's not storing a
primary definition (ie, rule source text).
        regards, tom lane


Re: Rule recompilation

From
Alex Pilosov
Date:
On Thu, 12 Jul 2001, Jan Wieck wrote:

> Alex Pilosov wrote:
> > I remember awhile ago, someone floated the idea of a dependency view which
> > would list all objects and what OIDs they have in their plan. (i.e. what
> > do they depend on).
> >
> > I'm definitely no expert in this, but to me, one possible implementation
> > would be to enhance outfuncs to provide for creation tracking of all
> > OIDs used in plan, and allow caller to receive this list and do something
> > with it. This would actually be very simple, as only _outOidList will need
> > to be modified...(but then again, I'm known for oversimplifying things :)
> >
> > Then, we can add  ev_depends/oidvector to pg_rewrite and store the
> > dependency there, and for stored procedures, add a prodepends/oidvector to
> > pg_proc.
> >
> > Then, create union of pg_rewrite and pg_proc to list dependencies.
> >
> > Then, we would be able to provide warning when an object is dropped:
> > 'The following objects depend on this blah blah', and possibly an action
> > "alter database fixdepends oid" which would recompile everything that
> > depends on that oid.
> >
> > How's this sound?
> 
>     Er - oversimplified :-)
Yeah, most of my ideas end up like that, however see below ;)
> 
>     I  remember  it well, because Bruce is mentioning it every so
>     often and constantly tries to convince me to start a  project
>     about a dependency table.  I just think it's better not to do
>     it for 7.2 (didn't we  wanted  to  have  that  released  THIS
>     year?).
>
>     Anyway,  there's  alot  more  to  look  at.  Functions can be
>     referenced in views, indexes, operators, aggregates and maybe
>     more  places.  Views/rules  can reference allmost any object.
>     And this only builds the permanent cross reference.

For views, the necessary information (what does a view depend on) is in
pg_rewrite anyway, which we can track with my proposal.

For indices/operators/aggregates, pg_depends view may simply union the
necessary information from the existing tables, no additional tracking is
necessary. (example, if index depends on a proc, we already have that proc
oid as indproc).

If you are talking that tracking nested dependencies is hard, I don't
disagree there, its a pain to do recursive queries in SQL, but the
solution is to have (non-sql) function list_deep_depend(oid) which would
recurse down the pg_depend and find what depends on an object...

>     We have to take a look at runtime information, telling  which
>     prepared/saved  SPI plan uses a particular object and trigger
>     automatic re-prepare for the plan in case.
This doesn't bother me that much. Restart of postmaster is an acceptable
thing to clear [really strange] things up.

I'm actually not looking for 100% recompilation when an underlying object
is changed, I'm looking for 100% reliable dependency information and a
warning listing all objects that will break if I delete an object.

Your proposal (automatic recompilation for rules) is orthogonal (but
related) to what I'm suggesting. Having an ability to recompile a rule is
great. Having an ability to see what rules depend on a given object is
also great. Having an ability to recompile all rules that depend on a
given object is even better ;) 

Having an ability to recompile _everything_ that depends on a given object
is priceless, but we can take that one step at a time, first tackling
rules...

>     For most objects, there is no such "recompile" possible -  at
>     least  not  without  storing  alot more information than now.
>     Create a function and based on that  an  operator.  Then  you
>     drop  the  function and create another one. Hmmm, pg_operator
>     doesn't have the function name and argument  types,  it  only
>     knows the old functions oid. How do you find the new function
>     from here? So  basically  we'd  need  some  sort  of  pg_dump
>     snippet  associated  with  every object and issue an internal
>     DROP/CREATE using that string to recompile it.

Which may not be all that hard now, as most things that pg_dump does now
are integrated in the backend, and all pg_dump does is call an appropriate
function (ala pg_get_viewdef/pg_get_ruledef).  But I am content leaving it
for the next time, tackling rules for now.




Re: Rule recompilation

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.com> writes:
>     You  cannot control Tcl's bytecode compiler from the outside.

An excellent example.  You don't *need* to control Tcl's bytecode
compiler from the outside, because *Tcl gets it right without help*.
It takes care of the function-text-to-derived-form dependency
internally: when you redefine the function, the internal form is
discarded and rebuilt.  You don't have to worry about it.

What everyone else is telling you is that we should strive to do the
same, not punt and make the user tell us when to recompile.
        regards, tom lane


Re: Rule recompilation

From
Jean-Michel POURE
Date:
IMHO we are trying to have a compiled language behave like an interpreted 
language.
This is a bottom to top approach with no real future. Here is a proposal of 
a top to bottom approach.

What we do in pgAdmin is that we store objects (functions, views and 
triggers) in separate tables called Development tables.
The production objects (which you are talking about) are running safe 
*without* modification. At any moment, it is possible to recompile the
development objects (functions, triggers and views modified by the user) 
from development tables.

pgAdmin then checks dependencies a goes through a whole compilation process.
BUT ONLY AT USER REQUEST.

Who would honestly work on a production server? This is too dangerous in a 
professional environment.
In a near future, we will offer the ability to store PostgreSQL objects on 
separate servers (called code repository).

You will then be able to move objects from the development server to the 
production servers. Think of replication.
Also, pgAdmin will include advanced team work features and code serialization.

pgAdmin is already an *old* product as we are working on exciting new things:
http://www.greatbridge.org/project/pgadmin/cvs/cvs.php/pgadmin/help/todo.html

Before downloading pgAdmin from CVS, read this:
http://www.greatbridge.org/project/pgadmin/cvs/cvs.php/binaries/readme.html

We are looking for feedback and help from the community.
Greetings from Jean-Michel POURE, Paris, France




Re: Rule recompilation

From
Hiroshi Inoue
Date:
Tom Lane wrote:
> 
> Jan Wieck <JanWieck@Yahoo.com> writes:
> 
> What everyone else is telling you is that we should strive to do the
> same, not punt and make the user tell us when to recompile.
> 

In Oracle, objects like views, functions and triggers are
just marked INVALID when an object to which they make
reference is changed. INVALID objects are recompiled when
they are needed. in particular, if a table was dropped and
a table is created with the same name then the objects which
make reference (directly/indirectly) to the table would
revive.
We would have to reconsider *alter table .. rename ..* ..

regards,
Hiroshi Inoue


Re: Rule recompilation

From
Tom Lane
Date:
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> We would have to reconsider *alter table .. rename ..* ..

Yeah, that's one thing that would act differently if we adopt my idea of
considering the source text of the rule to be the primary definition.
It's not clear if this is good or bad, however.  Consider:
create table foo (f1 int, f2 text);
create view v1 as select f1 from foo;
alter table foo rename column f1 to fx;
alter table foo rename column f2 to f1;

At this point, what would you expect v1 to return, and why?  How
would you justify it in terms of "what the user would expect",
as opposed to "what we can conveniently implement"?

Another interesting case is:
create table foo (f1 int, f2 text);
create view v1 as select * from foo;
alter table foo add column f3 float;

Should v1 now have three columns?  If not, how do you justify it?
If so, how do you implement it (v1 has already got its pg_attribute
rows)?

Messy any way you look at it, I fear.  But clearly my idea needs
more thought ...
        regards, tom lane


Re: Rule recompilation

From
Hiroshi Inoue
Date:
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > We would have to reconsider *alter table .. rename ..* ..
> 
> Yeah, that's one thing that would act differently if we adopt my idea of
> considering the source text of the rule to be the primary definition.
> It's not clear if this is good or bad, however.  Consider:
> 
>         create table foo (f1 int, f2 text);
> 
>         create view v1 as select f1 from foo;
> 
>         alter table foo rename column f1 to fx;
> 
>         alter table foo rename column f2 to f1;
> 
> At this point, what would you expect v1 to return, and why?  How
> would you justify it in terms of "what the user would expect",
> as opposed to "what we can conveniently implement"?
> 

The view v1 is INVALIDated by the first ALTER command.
It is still INVALID after the second ALTER command.
When *select * from v1* is called, the re-compilation 
would translate it into *select f1(originally f2) from foo*.
The behavior is different from that the current.
The current *reference by id* approach is suitable
for the current *rename* behavior but *reference by
name* approach isn't. *rename* isn't that easy from
the first IMHO.

> Another interesting case is:
> 
>         create table foo (f1 int, f2 text);
> 
>         create view v1 as select * from foo;
> 
>         alter table foo add column f3 float;
> 
> Should v1 now have three columns? 

Yes. We could create the view v1 as *select f1, f2 
from foo* from the first if we hate the side effect. 

> If not, how do you justify it?
> If so, how do you implement it (v1 has already got its pg_attribute
> rows)?
> 

Isn't the creation of pg_attribute tuples a part of
(re-)compilation ?

regards,
Hiroshi Inoue