Re: About "Allow VIEW/RULE recompilation when the underlying tables change"

I was dealing with a customer recently who very much wanted this behavior, during discussions with them
I wrote up a little something describing how different database vendors treat views and alter statements.
...

Part of the issue here is that the SQL Standard does a very poor job of expressing what correct behavior is of VIEWS when the underlying table is altered, as a result nearly every major database vendor has different behavior. <the customer> would be having similar (but slightly different) problems if they moved from almost any database to almost any other database.

Oracle: Treats all views as the text used to define them and allows for the possibility of "invalid" views
Terradata: Expands and fully qualifies the text used to define the views, but still treats them as text, and allows for the possibility of "invalid" views.
DB2: Treats views as logical and does not allow for the possibility of "invalid" views. It tries to allow ALTER statements but only under limited circumstances.
Postgres: Treats views as logical and does not allow for the possibility of "invalid" views. It tries to allow ALTER statements but only under limited circumstances (not the same circumstances as DB2).
Microsoft: Supports two different kinds of views.

These different approaches allow for different sorts of DDL operations to succeed and can leave views in different levels of usability.

ALTER TABLE example RENAME TO example_old;
- In oracle and Terradata views over "example" are now invalid.
- In Postgres and DB2 views over "example" continue to work even though the table has a different name.

DROP TABLE example;
- In oracle and Terradata views over "example" are now invalid.
- In Postgres and DB2 the DROP fails unless CASCADE is specified.

ALTER TABLE example SET SCHEMA new_schema;
- In Oracle the views become invalid unless the new schema is in the search path
- In Terradata the views become invalid
- In Postgres and DB2 the views still refer to the original table.

ALTER TABLE example ADD COLUMN new int;
- In Oracle views may return the new column
- In Terradata, Postgres, and DB2 the new column does not show up in existing views.

ALTER TABLE example DROP COLUMN old;
- In Oracle views may return fewer columns and/or become invalid
- In Terradata views that reference the stated column will become invalid (even when the view was simply "SELECT *").
- In Postgres and DB2 the ALTER statement will fail if the view references the specified column.

ALTER TABLE example RENAME COLUMN old TO new;
- In Oracle views will return different columns and/or become invalid, dependent views may become invalid.
- In Terradata views referencing the stated column become invalid.
- In Postgres, DB2 existing views will automatically update with the change.

ALTER TABLE example ALTER COLUMN old TYPE text;
- In Oracle and Terradata views may update automatically, or may become invalid.
- In DB2 views will try to rewrite themselves and may or may not fail depending on contents
- In Postgres the ALTER statement will fail if the view references the specified column.

Note that in the above NO database will always be able to keep views in sync with alterations to the underlying tables, this is because there is not a single well defined answer to how that update should occur. For every single database vendor certain types of update operations will require manual user intervention to go through the entire dependent view tree and manually fix the views under some circumstances. The question is only /which/ circumstances.

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.

There might be some slight inaccuracies above since I was going off documentation and extrapolation of the described behavior, but the general points still hold.
...

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.
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.
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.

Regards,
Caleb

On 12/18/09 8:28 PM, "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:

On Fri, Dec 18, 2009 at 11:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Dec 18, 2009 at 10:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> The TODO item is terribly underdocumented, but I think what it's on
>>> about is that right now we refuse commands like ALTER COLUMN TYPE if
>>> the column is referenced in a view. It would be nice to propagate
>>> such a change into views if possible.
>
>> I'm unconvinced that this is, as you would say, even mildly sane.
>
> I've updated the TODO item to link to this discussion, so at least the
> next three people who pop up with "let's just store the view source!"
> will have some idea of what they're up against.

Excellent. :-)

...Robert