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
