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