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