Обсуждение: script to keep views "correct"?
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
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
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
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