Thread: idea: storing view source in system catalogs
I wonder if there is any merit to the idea of storing the 'create view' statement that created the view in an appropriate place. There are basically two reasons for this: *) preserve initial formatting, etc. Database functions when viewed with \df+ in psql appear nice and clean as I wrote them. Much better than \d <view> *) store what the view intends to do, not what it does. If I do: create view v as select * from foo; The view definition as understood by the database expands the column list. This has unfortunately means that the view definition is no longer valid if the underlying type changes. Understandably, select * is often considered bad style but nevertheless often comes up when writing 'advanced' type manipulation that postgresql is so good at. This also comes up when expanding composite type for example. The idea here is to provide a stepping stone towards allowing the view to be redefined against source objects during invalidation events. ISTM the easiest and best way to do that is to try and reapply the original definition against the altered dependant objects and throw the resultant error, if any. Views can be a real pain to deal with than functions in terms of DDL operations. merlin
On Tue, May 20, 2008 at 02:03:17PM -0400, Merlin Moncure wrote: > I wonder if there is any merit to the idea of storing the 'create > view' statement that created the view in an appropriate place. > There are basically two reasons for this: +1 for DDL in general, including the original CREATE and appending all subsequent ALTERs. DROP would have to make the thing go away. I suppose CREATE OR REPLACE would also wipe the earlier versions, but I'm not married to to that idea. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
"Merlin Moncure" <mmoncure@gmail.com> writes:
> I wonder if there is any merit to the idea of storing the 'create
> view' statement that created the view in an appropriate place.
No, there isn't.
As counterexamples look at pg_constraint.consrc and pg_attrdef.adsrc,
both of which were mistakes from the day they were put in, and have
been deprecated for a long time. Source doesn't have any reasonable
way to track table/column renames, to point out one problem.
> If I do:
> create view v as select * from foo;
> The view definition as understood by the database expands the column
> list.
Indeed, exactly as is *required* by the SQL spec.
regards, tom lane
David Fetter wrote: > On Tue, May 20, 2008 at 02:03:17PM -0400, Merlin Moncure wrote: >> I wonder if there is any merit to the idea of storing the 'create >> view' statement that created the view in an appropriate place. >> There are basically two reasons for this: > > +1 for DDL in general, including the original CREATE and appending all > subsequent ALTERs. DROP would have to make the thing go away. I > suppose CREATE OR REPLACE would also wipe the earlier versions, but > I'm not married to to that idea. The 1000$ question is how to deal with renames, though. Not of view itself, but of the tables it depends on. Currently, the view tracks those renames (which is an important feature, IMHO), and you get the correct (using the new names) SQL when dumping the view. Anything that stores the original statement, but fails to track renames is more confusing that what it's worth, I think... But maybe you could store the whitespace appearing before (or after?) a token in the parse tree that is stored for a view. That might not allow reconstructing the *precise* statement, but at least the reconstructed statement would preserve newlines and indention - which probably is the whole reason for wanting to store the original statement in the first place, no? I have no idea how hard I'd be to carry that information from the lexer into the parser, and then into whatever representation we use for storing a view, though... regards, Florian Pflug
Florian Pflug wrote: > > But maybe you could store the whitespace appearing before (or after?) > a token in the parse tree that is stored for a view. That might not > allow reconstructing the *precise* statement, but at least the > reconstructed statement would preserve newlines and indention - which > probably is the whole reason for wanting to store the original > statement in the first place, no? I Not the whole reason. To get a view definition that is more readable, the pretty_bool option of pg_get_viewdef already does some newline and indent formatting. Not the initial formatting, but Good Enough (TM), I believe. What's really lost is any comment that might have existed in the initial source. I previously had the idea to invent comment nodes, but never came to implement them. Regards, Andreas
On Wed, May 21, 2008 at 4:39 AM, Andreas Pflug <pgadmin@pse-consulting.de> wrote: > Not the whole reason. To get a view definition that is more readable, the > pretty_bool option of pg_get_viewdef already does some newline and indent > formatting. Not the initial formatting, but Good Enough (TM), I believe. This is where I disagree. It really can make your sql quite unreadable, adding all kinds of extra casts and parenthesis and such. I am very particular about how my sql is formatted. merlin
On Wed, 2008-05-21 at 10:40 +0200, Andreas Pflug wrote:
> Florian Pflug wrote:
> >
> > But maybe you could store the whitespace appearing before (or after?)
> > a token in the parse tree that is stored for a view. That might not
> > allow reconstructing the *precise* statement, but at least the
> > reconstructed statement would preserve newlines and indention - which
> > probably is the whole reason for wanting to store the original
> > statement in the first place, no? I
>
>
> Not the whole reason. To get a view definition that is more readable,
> the pretty_bool option of pg_get_viewdef already does some newline and
> indent formatting. Not the initial formatting, but Good Enough (TM), I
> believe.
>
> What's really lost is any comment that might have existed in the initial
> source. I previously had the idea to invent comment nodes, but never
> came to implement them.
Is'nt a view roughly equivalent to a SQL language FUNCTION with no
arguments and a single select.
If it is so, then I can't see, why we can store the source for functions
but not for VIEWs
like this -
hannu=# create function viewfunc(out pg_proc) language sql as $$ select
* from pg_proc where proname = 'viewfunc' $$;
CREATE FUNCTION
hannu=# \x
Expanded display is on.
hannu=# select * from viewfunc();
-[ RECORD 1 ]--+---------------------------------------------------
proname | viewfunc
pronamespace | 2200
proowner | 10
prolang | 14
procost | 100
prorows | 0
proisagg | f
prosecdef | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 0
prorettype | 81
proargtypes |
proallargtypes | {81}
proargmodes | {o}
proargnames |
prosrc | select * from pg_proc where proname = 'viewfunc'
probin | -
proconfig |
proacl |
---------------
Hannu
On Wed, May 21, 2008 at 7:56 AM, Hannu Krosing <hannu@krosing.net> wrote: > On Wed, 2008-05-21 at 10:40 +0200, Andreas Pflug wrote: >> Florian Pflug wrote: >> > >> > But maybe you could store the whitespace appearing before (or after?) >> > a token in the parse tree that is stored for a view. That might not >> > allow reconstructing the *precise* statement, but at least the >> > reconstructed statement would preserve newlines and indention - which >> > probably is the whole reason for wanting to store the original >> > statement in the first place, no? I >> >> >> Not the whole reason. To get a view definition that is more readable, >> the pretty_bool option of pg_get_viewdef already does some newline and >> indent formatting. Not the initial formatting, but Good Enough (TM), I >> believe. >> >> What's really lost is any comment that might have existed in the initial >> source. I previously had the idea to invent comment nodes, but never >> came to implement them. > > Is'nt a view roughly equivalent to a SQL language FUNCTION with no > arguments and a single select. > > If it is so, then I can't see, why we can store the source for functions > but not for VIEWs That's what I'm saying. The behavior is a little different however. If you rename a column from under a function it will fail the next time the plan is generated while a view will track the column name in terms of how the view is defined to the backend. In other words, you can leave the function body alone because changing a column underlying a function has no side affects on the function body itself. It is blindly reapplied by the backend each time it's parsed and planned. So in a sense the function body stored in prosrc will never diverge from the parsed function since the parsed version is temporary. Views are different. They are parsed and the parsed version is stored in permanent fashion. Your comments would be right on the money if the view was re-parsed in each session...for things to work that way we would have to give up rename tracking of views. For the record, I think function behavior is better (i.e. not automatic name tracking on rename). In my ideal world, if I make alter table DDL, I would prefer to have all dependent objects recheck their source sql vs. the database and alert me of errors. This seems a better way to double check for dba mistakes although column rename can become a pain. However, I add columns, etc to tables _much_ more frequently than I rename them. merlin
I think the real problem here is that PostgreSQL is very finicky about what operations you can perform on a view. If I have a table foo and I define a view bar that uses foo and a view baz that uses bar, I can add a column to foo without a problem, and, similarly, I can also drop or alter a column in foo that is not used by bar. But the same is not true of bar. I can't make any changes at all to bar without dropping and recreating it, and that means I have to drop and recreate baz as well. If there are only two views involved, this is not so bad, but frequently there are a whole slough of views baz1, baz2, ..., bazn that all depend on bar, and I have to drop and recreate every single one of them. I could understand the need to do this if I were (for example) changing the type of some column that was used by all of these views, but that's usually not the case. Normally I'm just adding new columns to foo and bar, and none of the other views are changing... but they have to be recreated anyway. As a side note, handling this problem gracefully would go a long way to solving the original poster's concern about *-expansion. If updating to the latest version of "*" just required re-executing CREATE OR REPLACE VIEW ..., it would be relatively simple. As things stand now, it requires DROP VIEW ... CASCADE; CREATE OR REPLACE VIEW ...; followed by recreating all of the dependent objects. ...Robert
"Robert Haas" <robertmhaas@gmail.com> writes:
> I think the real problem here is that PostgreSQL is very finicky about
> what operations you can perform on a view. If I have a table foo and
> I define a view bar that uses foo and a view baz that uses bar, I can
> add a column to foo without a problem, and, similarly, I can also drop
> or alter a column in foo that is not used by bar. But the same is not
> true of bar.
Yeah. The current restrictions were set when CREATE OR REPLACE VIEW
was first implemented, and at that time we didn't have very much
ALTER TABLE capability at all; the view restrictions mirror what we
could do with a table at the time. It would be worth revisiting
that to make it square up with what you can now do to a table.
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > "Robert Haas" <robertmhaas@gmail.com> writes: >> I think the real problem here is that PostgreSQL is very finicky about >> what operations you can perform on a view. If I have a table foo and >> I define a view bar that uses foo and a view baz that uses bar, I can >> add a column to foo without a problem, and, similarly, I can also drop >> or alter a column in foo that is not used by bar. But the same is not >> true of bar. > > Yeah. The current restrictions were set when CREATE OR REPLACE VIEW > was first implemented, and at that time we didn't have very much > ALTER TABLE capability at all; the view restrictions mirror what we > could do with a table at the time. It would be worth revisiting > that to make it square up with what you can now do to a table. I thought the problem had more to do with the former lack of query invalidation. If someone altered the view we had no way to replan any plans from a former definition of the view. Now that we have the query cache would we know that the view had changed and therefore the whole query needs to be replanned from source? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> Yeah. The current restrictions were set when CREATE OR REPLACE VIEW
>> was first implemented, and at that time we didn't have very much
>> ALTER TABLE capability at all; the view restrictions mirror what we
>> could do with a table at the time. It would be worth revisiting
>> that to make it square up with what you can now do to a table.
> I thought the problem had more to do with the former lack of query
> invalidation. If someone altered the view we had no way to replan any plans
> from a former definition of the view.
Well, we had no way to replan plans that depended on characteristics of
tables, either, which meant that ALTER COLUMN TYPE was a pretty
dangerous feature too before 8.3. I don't see that altering the output
column set of a view is really much different.
> Now that we have the query cache would we know that the view had changed and
> therefore the whole query needs to be replanned from source?
Yeah, it's isomorphic AFAICS.
regards, tom lane
Merlin Moncure wrote: > On Wed, May 21, 2008 at 4:39 AM, Andreas Pflug > <pgadmin@pse-consulting.de> wrote: >> Not the whole reason. To get a view definition that is more readable, the >> pretty_bool option of pg_get_viewdef already does some newline and indent >> formatting. Not the initial formatting, but Good Enough (TM), I believe. > > This is where I disagree. It really can make your sql quite > unreadable, adding all kinds of extra casts and parenthesis and such. > I am very particular about how my sql is formatted. I agree to the disagreement ;-). Reading and editing views is not the most pleasing thing to do currently. Still, storing the original SQL is not the right thing to do IMHO - the only viable option I can see is trying to store plain-text nodes with the parse tree which get reinserted when the parse tree is converted back into a query. regards, Florian Pflug
Added to TODO: * Improve ability to modify views via ALTER TABLE http://archives.postgresql.org/pgsql-hackers/2008-05/msg00691.php --------------------------------------------------------------------------- Robert Haas wrote: > I think the real problem here is that PostgreSQL is very finicky about > what operations you can perform on a view. If I have a table foo and > I define a view bar that uses foo and a view baz that uses bar, I can > add a column to foo without a problem, and, similarly, I can also drop > or alter a column in foo that is not used by bar. But the same is not > true of bar. I can't make any changes at all to bar without dropping > and recreating it, and that means I have to drop and recreate baz as > well. If there are only two views involved, this is not so bad, but > frequently there are a whole slough of views baz1, baz2, ..., bazn > that all depend on bar, and I have to drop and recreate every single > one of them. > > I could understand the need to do this if I were (for example) > changing the type of some column that was used by all of these views, > but that's usually not the case. Normally I'm just adding new columns > to foo and bar, and none of the other views are changing... but they > have to be recreated anyway. > > As a side note, handling this problem gracefully would go a long way > to solving the original poster's concern about *-expansion. If > updating to the latest version of "*" just required re-executing > CREATE OR REPLACE VIEW ..., it would be relatively simple. As things > stand now, it requires DROP VIEW ... CASCADE; CREATE OR REPLACE VIEW > ...; followed by recreating all of the dependent objects. > > ...Robert > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +