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