Re: POSS. FEATURE REQ: "Dynamic" Views - Mailing list pgsql-general

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> How is this different from materialized views, which is already on the
> TODO list?

It's entirely unrelated.

Materialized views are about having all the data stored in advance. They're
really tables that have some sort of process to keep the data in them in sync
with other tables according to a view definition.

These "dynamic views" are really just normal views operationally. The only
difference is what happens to them when DDL changes are made to the objects
they depend on.

In normal SQL standard views column references are resolved at creation time
and continue to point to the same physical column despite column renames. And
"select *" doesn't change when new columns are added.

What these users and myself would prefer is something that remembers the
original view definition text and reinterprets it according to the new
definition of the underlying tables. So if I swap two columns by renaming them
I could recompile the view and it would swap which columns were used where.
And if I add new columns "select *" would include the new columns.

I'm starting to be a little skeptical about "CREATE DYNAMIC VIEW". I think
what would be better to proceed conservatively and just add a "ALTER VIEW
RECOMPILE". That at least gives the user a way to easily recover the original
intention without having to reenter the view definition manually.

It would also be useful to have a warning when any DDL is done to a column
being used in a view or adding a new column in any table where a view on the
table had a "select *". That would be useful independently of any automagic
recompile feature. Even if the user has to go fetch the original view
definition from his DDL file (which hopefully he saved) the warning will at
least make it more likely he'll remember to do so.


IF you find there's support for these ideas from the powers that be then the
TODOs would look something like:

o Add support for ALTER VIEW RECOMPILE which would recreate a view definition
  using the original SQL DDL definition that originally created it.

o Add warning whenever DDL to a table affects a view dependent on that table.
  Such as when a column is altered that is referenced in the view or when a
  column is added if a "select *" appears in the view.

o Add some option to CREATE VIEW to cause the above ALTER VIEW RECOMPILE to
  automatically happen whenever DDL to a table affects the view.

I think the first of these two are no-brainers if they're implemented well.
The third seems less likely to garner immediate support.

--
greg