Re: POSS. FEATURE REQ: "Dynamic" Views

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>, iharding(at)destinydata(dot)com, CSN <cool_screen_name90001(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: POSS. FEATURE REQ: "Dynamic" Views
Date: 2005-08-27 13:38:39
Message-ID: 200508271338.j7RDcds17173@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2005-08-27 14:06:40 Re: postgresql performance degradation over time....
Previous Message William Yu 2005-08-27 13:27:43 Re: Postgresql replication