Thread: Rule recompilation
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
> 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
> > 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
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
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
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) > >
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
> > 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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
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
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
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
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