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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

"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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers