Re: 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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

"Merlin Moncure" <mmoncure(at)gmail(dot)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, 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 4:39 AM, Andreas Pflug
<pgadmin(at)pse-consulting(dot)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, May 21, 2008 at 7:56 AM, Hannu Krosing <hannu(at)krosing(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Robert Haas" <robertmhaas(at)gmail(dot)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(at)enterprisedb(dot)com> writes:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)pse-consulting(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +