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


Thu, 12 Jul 2001 13:28:59 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Rule recompilation
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)
> 
> 




Thu, 12 Jul 2001 14:23:21 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: Rule recompilation
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 and maybe
    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 and trigger
    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 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.


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


Thu, 12 Jul 2001 14:39:21 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Rule recompilation
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

Thu, 12 Jul 2001 14:50:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rule recompilation 
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. Then write 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


Thu, 12 Jul 2001 15:10:19 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Rule recompilation
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

Thu, 12 Jul 2001 15:11:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rule recompilation 
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 candidates and 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


Thu, 12 Jul 2001 15:25:20 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Rule recompilation
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

Thu, 12 Jul 2001 15:39:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rule recompilation 
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

Thu, 12 Jul 2001 15:46:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rule recompilation 
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.



Thu, 12 Jul 2001 15:46:33 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: Rule recompilation
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


Thu, 12 Jul 2001 21:51:01 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Rule recompilation
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


Thu, 12 Jul 2001 16:05:05 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: Rule recompilation