Rule recompilation

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:

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

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?

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

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:

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

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

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

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

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

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 just holds 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. Plus recursively 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 solved if
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

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

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:

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 catalog that
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. That surely
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

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

Hello all,

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?

This is what the latest CVS version of pgAdmin does in a limited way:
http://www.greatbridge.org/project/pgadmin/cvs/cvs.php/binaries/readme.html

When a function is modified with DROP/CREATE, it is marked dirty.
pgAdmin checks dependencies between functions, triggers and views
and goes through a complete rebuilding process.

My database has more than 150 PL/pgSQL functions along with views and triggers.
A normal human cannot keep track of dependencies by his own means.

Dave Page and I added this feature to pgAdmin because we were normal humans
and could not wait too long. When will dependency tracking be available
server-side?

We are now working on more advanced features. See:
http://www.greatbridge.org/project/pgadmin/cvs/cvs.php/pgadmin/help/todo.html

Best regards,
Jean-Michel POURE
pgAdmin Development Team

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 <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:

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

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

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

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