I've been working on a brute force method of saving view and function
source when changes to an underlying object force that object to be
dropped. But I think there is a way for Postgres to handle this that
wouldn't be too hard to implement and would be extremely useful for us
users.
Problem: We need to change the last_name column of the people table from
varchar(30) to varchar(50). You issue the alter table command only to be
reminded that the people table is quite popular. It will likely be used
in dozens of views and many of those views will have dependent views,
and lets say there are some dependent functions too. And you have to
drop all of them if you want to alter your column. Once they are dropped
you can alter your column and then start digging through your source
code repository to rebuild all of those views and functions that you
just dropped.
Proposal: Add an invalid flag to pg_class. Invalid objects would be
ignored when doing dependency checks for DDL statements. And an
exception would be thrown when an invalid object is called.
This is similar to what Oracle does. And most Oracle tools have find and
compile invalid objects with a statement like:
ALTER VIEW foo RECOMPILE;
ALTER PACKAGE bar RECOMPILE BODY;
Oracle invalidates objects without warning. But maybe we could keep the
current behavior and add an invalidate option.
ALTER TABLE people ALTER last_name VARCHAR(50);
-- Throw exception can not alter table with dependents
ALTER TABLE people ALTER last_name VARCHAR(50) INVALIDATE;
-- Alters column and invalidates any dependent objects
Is this a viable option?
Scott Bailey
