Re: ALTER TABLE should change respective views

On Tue, May 5, 2009 at 2:17 PM, Josh Berkus <josh@agliodbs.com> wrote:
> 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.

You can already add a column to a table or change a constraint without
needing to drop and recreate dependent views or functions.  You can
also rename and drop columns.  I think the real issue is when you have
dependencies on a VIEW.

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00691.php

I currently handle the problem you're describing by having a series of
scripts which automatically drop pretty much every view in the
database in reverse order of creation, and then recreate them all (all
within a single transaction).  I run it every time I do a release and
it works great, but it's definitely not ideal, and wouldn't work at
all but for the fact that my system is sufficiently lightly loaded
that taking locks on all of those views is actually possible.

8.4 will be slightly better than 8.3 in that it will allow CREATE OR
REPLACE VIEW to add additional columns to the end of the view
definition.

http://archives.postgresql.org/pgsql-committers/2008-12/msg00066.php

...but this still falls considerably short of where I'd like to be.
The next logical step would probably be to support ALTER VIEW DROP
COLUMN, but I haven't really looked at what would be required to
implement that.  Checking the dependencies is probably the easy part;
the tricky things, I think, are (a) currently, attisdropped can never
be set for any column of a view, does anything break if we change
this? and (b) how do we modify the stored view definition to remove
the dropped column from the query's target list?

...Robert