Thread: view management
I have a question about view management... I often have need for views that reference views that reference views, and so on. When I need to make a small update to one of the views, I am faced with having to drop and recreate all dependent views even if the driving change just adds another column to the view, for example. I might have to drop and recreate many tens of views just to make a change to a single view. What a PITA. How do others manage this? TIA. Ed
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 16 Nov 2007 13:57:24 -0700
"Ed L." <pgsql@bluepolka.net> wrote:
> I have a question about view management...
>
> I often have need for views that reference views that reference
> views, and so on. When I need to make a small update to one of
> the views, I am faced with having to drop and recreate all
> dependent views even if the driving change just adds another
> column to the view, for example. I might have to drop and
> recreate many tens of views just to make a change to a single
> view. What a PITA. How do others manage this?
I use stored procedures instead.
Joshua D. Drake
>
> TIA.
> Ed
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 9: In versions below 8.0,
> the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHPgUMATb/zqfZUUQRAneuAJ9I4FByzPC3fSCwWdwjhEfQtdFpKwCfYDkv
IvL6KnBrOeqpUj6qKBkd8iE=
=Se2S
-----END PGP SIGNATURE-----
On Friday 16 November 2007 1:57 pm, Ed L. wrote: > I have a question about view management... > > I often have need for views that reference views that > reference views, and so on. When I need to make a small > update to one of the views, I am faced with having to drop and > recreate all dependent views even if the driving change just > adds another column to the view, for example. I might have to > drop and recreate many tens of views just to make a change to > a single view. What a PITA. How do others manage this? And before you tell me all about DROP VIEW ... CASCADE, please note I'm not talking about that. I'm talking about the difficulties of having to recreate all views in the entire subtree of view dependencies just to change one minor aspect of an independent view. TIA... Ed
On Fri, Nov 16, 2007 at 02:02:37PM -0700, Ed L. wrote: > subtree of view dependencies just to change one minor aspect of > an independent view. Well, it's not independent, if other things depend on it, is it? :) A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke
On Nov 16, 2007 4:02 PM, Ed L. <pgsql@bluepolka.net> wrote: > On Friday 16 November 2007 1:57 pm, Ed L. wrote: > > I have a question about view management... > > > > I often have need for views that reference views that > > reference views, and so on. When I need to make a small > > update to one of the views, I am faced with having to drop and > > recreate all dependent views even if the driving change just > > adds another column to the view, for example. I might have to > > drop and recreate many tens of views just to make a change to > > a single view. What a PITA. How do others manage this? > > And before you tell me all about DROP VIEW ... CASCADE, please > note I'm not talking about that. I'm talking about the > difficulties of having to recreate all views in the entire > subtree of view dependencies just to change one minor aspect of > an independent view. you have to rig a build system. if you have a lot of views (which is good), and keeping them up to date is a pain, you have to automate their creation. simplest way to do that is to rig a build system around sql scripts. when you create a view the first time, save it's creation script in a .sql file and replay that when you need it. if you like to get fancy, you can always work solutions around make, etc on top of this. there are other tricks...for example you could grep object dropped by the database and replay them. avoid gui tools for heavy management...the are the antithesis of this kind of approach. merlin
On Nov 16, 2007 4:01 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > On Fri, 16 Nov 2007 13:57:24 -0700 > "Ed L." <pgsql@bluepolka.net> wrote: > > > I have a question about view management... > > > > I often have need for views that reference views that reference > > views, and so on. When I need to make a small update to one of > > the views, I am faced with having to drop and recreate all > > dependent views even if the driving change just adds another > > column to the view, for example. I might have to drop and > > recreate many tens of views just to make a change to a single > > view. What a PITA. How do others manage this? > > I use stored procedures instead. IMO, this approach has a lot of problems...not only does it force you to think of your database access in terms of inputs and outputs in advance. Furthermore it can force your queries using the functions into awkward or suboptimal plans. I think functions are appropriate for certain tasks that are better handled in procedural manner for various reasons, but it's very good style to keep applications interfacing to the database as much as possible through views. All procedure access is ok, but is too much abstraction and creates headaches down the line. Furthermore, it hides the problem asked by the OP, not solves it, since the database merely forces you to check the dependencies by creating the view, whereas functions displace that check down the line which might result in missed dependency issues. merlin
On Friday 16 November 2007 2:09 pm, Merlin Moncure wrote:
> you have to rig a build system. if you have a lot of views
> (which is good), and keeping them up to date is a pain, you
> have to automate their creation. simplest way to do that is to
> rig a build system around sql scripts. when you create a view
> the first time, save it's creation script in a .sql file and
> replay that when you need it. if you like to get fancy, you
> can always work solutions around make, etc on top of this.
> there are other tricks...for example you could grep object
> dropped by the database and replay them.
That looks about as ugly as can be. Ugh. What it appears to
boil down to is that views become unusable unless you are
willing to invest the effort in a complex build system. The DB
should handle this issue automatically. Does Oracle?
There is a slightly related todo item:
-------
# Allow VIEW/RULE recompilation when the underlying tables change
Another issue is whether underlying table changes should be
reflected in the view, e.g. should SELECT * show additional
columns if they are added after the view is created.
-------
Looks like a tricky problem. What I think is needed is some sort
of column-level linkage tracking between views, rather than just
view-level dependencies. For example,
create table foo (id integer, msg varchar);
create table bar (id integer, msg varchar);
create view fooview as select id from foo;
create view barview as
select b.*, f.id as fooid
from bar b join fooview f on b.id = f.id;
When barview is created, f.id would need to be noted as depending
on fooview.id rather than just noting barview depends on
fooview. Then, if someone decides to recreate fooview to
include foo.msg, it can be discerned that the change will not
impact barview at all. That approach looks far too simple to
have not been done already.
On Nov 16, 2007 3:43 PM, Ed L. <pgsql@bluepolka.net> wrote: > > That looks about as ugly as can be. Ugh. What it appears to > boil down to is that views become unusable unless you are > willing to invest the effort in a complex build system. The DB > should handle this issue automatically. Does Oracle? Really? I find a build system to be a pretty necessary part of enterprise development. I can't imagine trying to keep track of what I've done to my db without using some kind of simple .sql scripts with all my ddl in them. And I use views and user defined functions a lot.
On Nov 16, 2007 4:43 PM, Ed L. <pgsql@bluepolka.net> wrote: > On Friday 16 November 2007 2:09 pm, Merlin Moncure wrote: > > you have to rig a build system. if you have a lot of views > > (which is good), and keeping them up to date is a pain, you > > have to automate their creation. simplest way to do that is to > > rig a build system around sql scripts. when you create a view > > the first time, save it's creation script in a .sql file and > > replay that when you need it. if you like to get fancy, you > > can always work solutions around make, etc on top of this. > > there are other tricks...for example you could grep object > > dropped by the database and replay them. > > That looks about as ugly as can be. Ugh. What it appears to > boil down to is that views become unusable unless you are > willing to invest the effort in a complex build system. The DB > should handle this issue automatically. Does Oracle? it's not as bad as it looks...but simply keeping your 'create views' in scripts should be enough. other advantages include ability to track schema object changes in source code. > There is a slightly related todo item: > # Allow VIEW/RULE recompilation when the underlying tables change > > Another issue is whether underlying table changes should be > reflected in the view, e.g. should SELECT * show additional > columns if they are added after the view is created. personally, what I would like would be to have the original text of the view to be stored and replayed by the database in this scenario. if you do '\d' on a view you may notice that the definition, while correct, is significantly mangled for some types of queries. this would also solve the 'select *' issue, which is extremely desirable in some cases and you could control it in the original create view statement. merlin
On Fri, Nov 16, 2007 at 02:43:01PM -0700, Ed L. wrote: > That looks about as ugly as can be. Ugh. What it appears to > boil down to is that views become unusable unless you are > willing to invest the effort in a complex build system. The DB You're kidding, right? You don't think that a build system, along with change control, for your schema is a good thing? A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke
On Friday 16 November 2007 2:48 pm, Scott Marlowe wrote: > On Nov 16, 2007 3:43 PM, Ed L. <pgsql@bluepolka.net> wrote: > > That looks about as ugly as can be. Ugh. What it appears > > to boil down to is that views become unusable unless you are > > willing to invest the effort in a complex build system. The > > DB should handle this issue automatically. Does Oracle? > > Really? I find a build system to be a pretty necessary part > of enterprise development. I can't imagine trying to keep > track of what I've done to my db without using some kind of > simple .sql scripts with all my ddl in them. > > And I use views and user defined functions a lot. The overall schema upgrade management system is not the difficult part. I find the difficulty comes with, for example, 5 levels of view dependencies. The view you want to update requires you to rebuild 15 others, which in turn requires you to trace back another 15 views, and so on until you reach the leafs of the tree. You don't know those dependencies when you create the first few views. Maybe you just manually discover all these dependency paths each time you decide to change a view with dependencies. That's the part I'm griping about and for which I was hoping for a better way.
Ed L. wrote: > On Friday 16 November 2007 2:48 pm, Scott Marlowe wrote: > >> On Nov 16, 2007 3:43 PM, Ed L. <pgsql@bluepolka.net> wrote: >> >>> That looks about as ugly as can be. Ugh. What it appears >>> to boil down to is that views become unusable unless you are >>> willing to invest the effort in a complex build system. The >>> DB should handle this issue automatically. Does Oracle? >>> >> Really? I find a build system to be a pretty necessary part >> of enterprise development. I can't imagine trying to keep >> track of what I've done to my db without using some kind of >> simple .sql scripts with all my ddl in them. >> >> And I use views and user defined functions a lot. >> > > The overall schema upgrade management system is not the difficult > part. I find the difficulty comes with, for example, 5 levels > of view dependencies. The view you want to update requires you > to rebuild 15 others, which in turn requires you to trace back > another 15 views, and so on until you reach the leafs of the > tree. You don't know those dependencies when you create the > first few views. Maybe you just manually discover all these > dependency paths each time you decide to change a view with > dependencies. That's the part I'm griping about and for which I > was hoping for a better way. > We have a system that has quite a few views to access some of the data (although we purposely tried to avoid views that pulled from other view due to some performance issues), but when we had all of the view interdependencies, we had a simple shell script that ran through a list of SQL files and imported them one after the other. If we every had to drop a view that cascaded to other dependent views, we would just do the DROP VIEW ... CASCADE, then run the shell script to recreate all of the views. In your situation, the time consuming part would be the initial creation of the script to get the SQL files in the correct order. After that is done, it's just a matter of proper maintenance to keep it working. Justin
Thanks, Justin. On Friday 16 November 2007 4:38 pm, Justin Pasher wrote: > We have a system that has quite a few views to access some of > the data (although we purposely tried to avoid views that > pulled from other view due to some performance issues), but > when we had all of the view interdependencies, we had a simple > shell script that ran through a list of SQL files and imported > them one after the other. If we every had to drop a view that > cascaded to other dependent views, we would just do the DROP > VIEW ... CASCADE, then run the shell script to recreate all of > the views. In your situation, the time consuming part would be > the initial creation of the script to get the SQL files in the > correct order. After that is done, it's just a matter of > proper maintenance to keep it working.
Em Friday 16 November 2007 18:57:24 Ed L. escreveu: > > I often have need for views that reference views that reference > views, and so on. When I need to make a small update to one of > the views, I am faced with having to drop and recreate all > dependent views even if the driving change just adds another > column to the view, for example. I might have to drop and > recreate many tens of views just to make a change to a single > view. What a PITA. How do others manage this? I have the same problem. DB2 does have some kind of path invalidation where you have to recreate the path to make your views usable again. So, it is possible to work with views like "SELECT * FROM table" to leverage table changes and things like that. If PostgreSQL had similar resources to "block" the usage of a (some) view(s) until another command was issued to prevent that need of dropping and recreating all dependent objects it would be great. A suboptimal alternative would be allowing the number of columns be greater than it was before -- i.e., add new columns to the view --, but block name changes for those columns and removing columns. -- Jorge Godoy <jgodoy@gmail.com>