Re: POSS. FEATURE REQ: "Dynamic" Views

Well, I just added to TODO:

    * Allow VIEW/RULE recompilation when the underlying tables change

Is dynamic view a industry-standard name?  If so, I will add it to the
TODO.


Updated TODO is:

    * Allow VIEW/RULE recompilation when the underlying tables change

      Another issue is whether underlying table changes should be reflected
      in the view, e.g. should SELECT * show additional columns if they
      are added after the view is created.

---------------------------------------------------------------------------

Greg Stark wrote:
>
> 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
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073