Re: ALTER TABLE should change respective views

All,

I was discussing this with a client who experiences this problem on a 
weekly basis, and the issue is mainly one of change management.

That is, manually dropping all of the views & functions dependant on a 
table, changing the table, and recreating the views and functions, is a 
major PITA and substantially inhibits the use of views and functions for 
security and database abstraction.  Add OID invalidation for cached 
plans into this and you have a bunch of developers taking their business 
logic out of the database and putting it into middleware.

What would solve the issue for 90% of our users would be an ALTER TABLE 
... CASCADE which would apply the changes to the table, and do a REPLACE 
VIEW and REPLACE FUNCTION for every dependant view and function, failing 
and rolling back if any REPLACE doesn't work automatically.

Incompatible table changes would still require manual drop and 
recreation, of course.  But most table changes to a production database 
are adding fields or changing constraints, which in most cases won't 
break dependant views or functions.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com