Re: POSS. FEATURE REQ: "Dynamic" Views - Mailing list pgsql-general
From | Bruce Momjian |
---|---|
Subject | Re: POSS. FEATURE REQ: "Dynamic" Views |
Date | |
Msg-id | 200508271338.j7RDcds17173@candle.pha.pa.us Whole thread Raw |
In response to | Re: POSS. FEATURE REQ: "Dynamic" Views (Greg Stark <gsstark@mit.edu>) |
Responses |
Re: POSS. FEATURE REQ: "Dynamic" Views
|
List | 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@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
pgsql-general by date: