On Sat, Dec 19, 2009 at 1:56 PM, Caleb Welton <cwelton@greenplum.com> wrote:
> I maintain that the approaches that inform the user that they have met that
> condition via ALTER statement failures (Postgres/DB2/Microsoft Bound Views)
> have certain advantages over databases that allow the update but may
> silently leave views in an usable state (Oracle, Terradata), in that at
> least the user Knows when they have to re-examine their views.
Agreed.
> As far as I can tell there are three approaches that could be taken to help
> address this problem:
> 1) DB2 like approach - try to perform rewrites where able, but if the
> rewrite fails then the alter operation fails. Would allow simple edits such
> as ALTER TYPE that are only changes in typmod, or if done more ambitiously
> would allow numbers to be changed to other numbers. But as Robert says this
> quickly approaches the territory of black magic.
And it can easily lead to silent breakage - e.g. if you change an
integer column to text, the view's attempt to coerce the text back to
integer will continue working as long as that coercion is valid for
all the data the view examines, but you have to think the user had a
reason for changing the type...
> 2) Microsoft like approach - create a new kind of view that is just stored
> as the view text and can become invalid. The people who want this type of
> view can use it combined with all the headaches associated with this type of
> view.
This could be emulated fairly easily. Just create a table with all
your view definitions in it and write a function that drops and
recreates them all. Call it whenever you change anything.
> 3) We extend things in a way that just makes dropping and recreating views
> more convenient. E.G. Some syntax for "drop all dependents" would be
> helpful to make schema changes easier.
How is that different from CASCADE?
...Robert
