SEO

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend