Scott,
I implemented a system exactly like this for the app in my signature below.
Basically the system stores metadata containing each view definition and the
joins between them. When someone needs to alter a view, say to remove a column,
a DROP CASCADE is performed then each view is recreated in turn *in the correct
order*. Everything's in a transaction, so a failure of recreation will roll
back to the original state. This can of course happen if for example a column
is deleted which other views use. This method is only used if the original
attempt fails due to dependency errors - some view updates can work just fine
anyway.
I can point you to the relevant code in GitHub if you're interested (it's Java).
Regards
Oliver Kohll
oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company
On 24 Apr 2010, at 13:01, Scott Bailey arta...@comcast.net wrote:
Using views in Postgres can be a painful process. Changing a column in a base
table will require you to drop all views that depend on it, and all views
that depend on those views and so on.
My coworker was complaining this morning that he now has a bunch of queries
where a view is joined back on the original table to add a column that was
missing from the view. It was easier to do this than to drop the view and all
of it's dependencies and then find all the source code and rebuild all of the
views in the correct order.
So my thought was to create an invalid objects table to store the source and
dependencies (and possibly permissions) when a DDL change invalidates a view
or a function. And later you can call a procedure that (tries to) rebuild
those invalid objects.
My initial plan of attack is to just create a function that stores the
information required to rebuild the dependencies before dropping them.
Something like:
store_and_drop('my_view_name')
I'm thinking that ultimately it would be nice if postgres could do this
automatically. Maybe:
DROP my_view_name CASCADE WITH RESTORE
So before I begin, has anyone already done this? And does anyone have any
advice as to how it may best be done?
Thanks
Scott Bailey
