Thread: script to keep views "correct"?

script to keep views "correct"?

From
Tom Jenkins
Date:
Hello all,
is there a script somewhere that ensures that changing a view that other
views depend on, will recreate those other views?  in our development
server we're constantly reworking/tweaking views and invariably forget
to recreate these other views and end up getting cache lookup errors.

same thing with functions/triggers
--

Tom Jenkins
Development InfoStructure
http://www.devis.com





Re: script to keep views "correct"?

From
Gregory Seidman
Date:
Tom Jenkins sez:
} Hello all,
} is there a script somewhere that ensures that changing a view that other
} views depend on, will recreate those other views?  in our development
} server we're constantly reworking/tweaking views and invariably forget
} to recreate these other views and end up getting cache lookup errors.
}
} same thing with functions/triggers

I have also been looking for something similar. Basically, whenever a view
(or whatever) gets created, the table/view/function names are resolved and
it is "compiled." This is great for speed, but if you change something that
other things depend upon, they fall apart. In particular, I had a table for
which one of the columns had a default that took its value from a function.
When I changed the function, it was no longer possible to insert into the
table until I did an ALTER TABLE ALTER COLUMN SET DEFAULT.

It is absolutely reasonable that this is how things work, but it would be
really good if some dependency-sensitive script could generate the SQL to
regenerate/adjust all of the things that might depend on each other. I
figure that it is difficult or impossible to see what changed and broke
what, but just assuming that everything changed, there should be a way to
spit out appropriate SQL to regenerate (in this order?):

1. views
2. functions
3. triggers/rules
4. column defaults

Obviously, views that depended on one another would have to be listed in a
topological sort. I think that pg_dump actually does most of this with
appropriate flags, but at least column defaults would have to be handled
differently (since we *really* don't want to drop/create the table, just
set its column defaults).

} Tom Jenkins
--Greg




Re: script to keep views "correct"?

From
Richard Huxton
Date:
On Thursday 27 Jun 2002 3:25 pm, Tom Jenkins wrote:
> Hello all,
> is there a script somewhere that ensures that changing a view that other
> views depend on, will recreate those other views?
> same thing with functions/triggers

I believe there's work going on to create a dependencies table, but of course
that's a lot of individual changes to make sure everything writes to it, so I
don't think it's imminent.

I try to keep all my source SQL/functions outside the database and manage them
with make. Not ideal, but the best I've come up with so far.

- Richard Huxton



Re: script to keep views "correct"?

From
Tom Jenkins
Date:
On Thu, 2002-06-27 at 13:34, Richard Huxton wrote:
> On Thursday 27 Jun 2002 3:25 pm, Tom Jenkins wrote:
> > Hello all,
> > is there a script somewhere that ensures that changing a view that other
> > views depend on, will recreate those other views?
> > same thing with functions/triggers
>
> I believe there's work going on to create a dependencies table, but of course
> that's a lot of individual changes to make sure everything writes to it, so I
> don't think it's imminent.

that will be very nice.

>
> I try to keep all my source SQL/functions outside the database and manage them
> with make. Not ideal, but the best I've come up with so far.
>

hrmmm... haven't thought about make

although pg_rewrite seems to have the info i want; i just have to parse
it


> - Richard Huxton
--

Tom Jenkins
Development InfoStructure
http://www.devis.com