Re: Invalid objects

[Oliver Kohll - Mailing Lists <oliver.lists@xxxxxxxxxx>]

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

oliver@xxxxxxxxxxxxxxx / +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 <artacus@xxxxxxxxxxx> 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