Hi,

Would it be possible to add a new attribute to pg_views that stores the
original view definition, as entered via SQL?

This would make the lives of those of us who make admin interfaces a lot
easier...

Chris


Tue, 16 Jul 2002 13:42:34 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
pg_views.definition
Christopher Kings-Lynne wrote:
> Hi,
> 
> Would it be possible to add a new attribute to pg_views that stores the
> original view definition, as entered via SQL?
> 
> This would make the lives of those of us who make admin interfaces a lot
> easier...

We actually reverse it on the fly:
	
	test=> \d xx
	         View "xx"
	 Column  | Type | Modifiers 
	---------+------+-----------
	 relname | name | 
	View definition: SELECT pg_class.relname FROM pg_class;

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 16 Jul 2002 01:45:12 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_views.definition
> We actually reverse it on the fly:
>
> 	test=> \d xx
> 	         View "xx"
> 	 Column  | Type | Modifiers
> 	---------+------+-----------
> 	 relname | name |
> 	View definition: SELECT pg_class.relname FROM pg_class;

Well, no - that's just dumping out the parsed form.

eg.

test=# create view v as select 1 in (1,2,3,4);
CREATE
test=# select * from v;
 ?column?
----------
 t
(1 row)

test=# \d v
            View "v"
  Column  |  Type   | Modifiers
----------+---------+-----------
 ?column? | boolean |
View definition: SELECT ((((1 = 1) OR (1 = 2)) OR (1 = 3)) OR (1 = 4));

It's really annoying when people save their view definition in phpPgAdmin
and when they load it up again it's lost all formatting.  Functions and
rules, for instance keep the original formatting somewhere.

Chris


Tue, 16 Jul 2002 14:51:41 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: pg_views.definition
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> It's really annoying when people save their view definition in phpPgAdmin
> and when they load it up again it's lost all formatting.  Functions and
> rules, for instance keep the original formatting somewhere.

Rules do not.  (A view is just a rule anyway.)

Functions do, but that's because their definition is entered as a text
string, which leads directly to those quoting headaches that you're
all too familiar with.

I've thought occasionally about improving the lexer so that parsetree
nodes could be tagged with the section of the source text they were
built from (probably in the form of a (start offset, end offset) pair).
This was mainly for use in improving error reporting in the
parse-analysis phase, but it might be useful for storing original source
text for rules too.

			regards, tom lane

Tue, 16 Jul 2002 09:31:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_views.definition 
Bruce Momjian wrote:
> 
> Christopher Kings-Lynne wrote:
> > Hi,
> >
> > Would it be possible to add a new attribute to pg_views that stores the
> > original view definition, as entered via SQL?
> >
> > This would make the lives of those of us who make admin interfaces a lot
> > easier...
> 
> We actually reverse it on the fly:

We do, but as soon as you break the view by dropping an underlying
object it fails to reconstruct. So having the original view definition
at hand could be useful for some ALTER VIEW RECOMPILE command.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Tue, 16 Jul 2002 10:22:20 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: pg_views.definition
Jan Wieck <JanWieck@Yahoo.com> writes:
>> We actually reverse it on the fly:

> We do, but as soon as you break the view by dropping an underlying
> object it fails to reconstruct. So having the original view definition
> at hand could be useful for some ALTER VIEW RECOMPILE command.

Note that the assumptions underlying this discussion have changed in
CVS tip: you can't break a view by dropping underlying objects.

regression=# create table foo(f1 int, f2 text);
CREATE TABLE
regression=# create view bar as select * from foo;
CREATE VIEW
regression=# drop table foo;
NOTICE:  rule _RETURN on view bar depends on table foo
NOTICE:  view bar depends on rule _RETURN on view bar
ERROR:  Cannot drop table foo because other objects depend on it
        Use DROP ... CASCADE to drop the dependent objects too

or

regression=# drop table foo cascade;
NOTICE:  Drop cascades to rule _RETURN on view bar
NOTICE:  Drop cascades to view bar
DROP TABLE
-- bar is now gone

Auto reconstruction of a view based on its original textual definition
is still potentially interesting, but I submit that it won't necessarily
always give the right answer.

			regards, tom lane

Tue, 16 Jul 2002 12:05:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_views.definition 
Tom Lane wrote:
> Auto reconstruction of a view based on its original textual definition
> is still potentially interesting, but I submit that it won't necessarily
> always give the right answer.

Sure, it's another bullet to shoot yourself into someone elses foot.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Tue, 16 Jul 2002 13:05:00 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: pg_views.definition
Jan Wieck wrote:
> Tom Lane wrote:
> > Auto reconstruction of a view based on its original textual definition
> > is still potentially interesting, but I submit that it won't necessarily
> > always give the right answer.
> 
> Sure, it's another bullet to shoot yourself into someone elses foot.

Do we want this on TODO?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 16 Jul 2002 13:07:43 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_views.definition
> > We do, but as soon as you break the view by dropping an underlying
> > object it fails to reconstruct. So having the original view definition
> > at hand could be useful for some ALTER VIEW RECOMPILE command.
> 
> Note that the assumptions underlying this discussion have changed in
> CVS tip: you can't break a view by dropping underlying objects.
> 
> regression=# create table foo(f1 int, f2 text);
> CREATE TABLE
> regression=# create view bar as select * from foo;
> CREATE VIEW
> regression=# drop table foo;
> NOTICE:  rule _RETURN on view bar depends on table foo
> NOTICE:  view bar depends on rule _RETURN on view bar
> ERROR:  Cannot drop table foo because other objects depend on it
>         Use DROP ... CASCADE to drop the dependent objects too

Hrm - looks like we really need CREATE OR REPLACE VIEW...

Chris


Wed, 17 Jul 2002 09:31:29 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: pg_views.definition 
On Wed, 17 Jul 2002, Christopher Kings-Lynne wrote:

> > > We do, but as soon as you break the view by dropping an underlying
> > > object it fails to reconstruct. So having the original view definition
> > > at hand could be useful for some ALTER VIEW RECOMPILE command.
> > 
> > Note that the assumptions underlying this discussion have changed in
> > CVS tip: you can't break a view by dropping underlying objects.
> > 
> > regression=# create table foo(f1 int, f2 text);
> > CREATE TABLE
> > regression=# create view bar as select * from foo;
> > CREATE VIEW
> > regression=# drop table foo;
> > NOTICE:  rule _RETURN on view bar depends on table foo
> > NOTICE:  view bar depends on rule _RETURN on view bar
> > ERROR:  Cannot drop table foo because other objects depend on it
> >         Use DROP ... CASCADE to drop the dependent objects too
> 
> Hrm - looks like we really need CREATE OR REPLACE VIEW...

I have written a patch for this. It is in an old source tree. I intend on
getting it together by august, along with create or replace trigger.

Gavin


Wed, 17 Jul 2002 11:44:39 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: pg_views.definition 
> > Hrm - looks like we really need CREATE OR REPLACE VIEW...
>
> I have written a patch for this. It is in an old source tree. I intend on
> getting it together by august, along with create or replace trigger.

Sweet.  I was going to email to see if you had a copy of your old create or
replace function patch that I could convert.  (Just as soon as this drop
column stuff is done.)

Chris


Wed, 17 Jul 2002 10:07:44 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: pg_views.definition 
Christopher Kings-Lynne wrote:
>>>We do, but as soon as you break the view by dropping an underlying
>>>object it fails to reconstruct. So having the original view definition
>>>at hand could be useful for some ALTER VIEW RECOMPILE command.
>>
>>Note that the assumptions underlying this discussion have changed in
>>CVS tip: you can't break a view by dropping underlying objects.
>>
>>regression=# create table foo(f1 int, f2 text);
>>CREATE TABLE
>>regression=# create view bar as select * from foo;
>>CREATE VIEW
>>regression=# drop table foo;
>>NOTICE:  rule _RETURN on view bar depends on table foo
>>NOTICE:  view bar depends on rule _RETURN on view bar
>>ERROR:  Cannot drop table foo because other objects depend on it
>>        Use DROP ... CASCADE to drop the dependent objects too
> 
> 
> Hrm - looks like we really need CREATE OR REPLACE VIEW...

The problem is that you would still need to keep a copy of your view 
around to recreate it if you wanted to drop and recreate a table it 
depends on. I really like the idea about keeping the original view 
source handy in the system catalogs.

It is common in Oracle to have dependent objects like views and packages 
get invalidated when something they depend on is dropped/recreated. 
Would it make sense to do something like that? I.e. set a relisvalid 
flag to false, and generate an ERROR telling you to recompile the object 
if you try to use it while invalid.

Joe



Tue, 16 Jul 2002 20:06:33 -0700
Joe Conway <mail@joeconway.com>
Re: pg_views.definition
Joe Conway wrote:
> The problem is that you would still need to keep a copy of your view
> around to recreate it if you wanted to drop and recreate a table it
> depends on. I really like the idea about keeping the original view
> source handy in the system catalogs.

This has been the case all the time. I only see an attempt to
make this impossible with the new dependency system. If I *must*
specify CASCADE to drop an object, my view depends on, my view
will be gone. If I don't CASCADE, I cannot drop the object.

So there is no way left to break the view temporarily (expert
mode here, I know what I do so please let me) and fix it later by
just reparsing the views definition.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being
right. #
# Let's break this rule - forgive
me.                                  #
#==================================================
JanWieck@Yahoo.com #

Wed, 17 Jul 2002 03:56:55 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: pg_views.definition
On Wed, 2002-07-17 at 09:56, Jan Wieck wrote:
> Joe Conway wrote:
> > The problem is that you would still need to keep a copy of your view
> > around to recreate it if you wanted to drop and recreate a table it
> > depends on. I really like the idea about keeping the original view
> > source handy in the system catalogs.
> 
> This has been the case all the time. I only see an attempt to
> make this impossible with the new dependency system. If I *must*
> specify CASCADE to drop an object, my view depends on, my view
> will be gone. If I don't CASCADE, I cannot drop the object.
> 
> So there is no way left to break the view temporarily (expert
> mode here, I know what I do so please let me) and fix it later by
> just reparsing the views definition.

As somebody said, this is the place where CREATE OR REPLACE TABLE could
be useful. (IMHO it should recompile dependent views/rules/...
automatically or mark them as broken if compilation fails)

-------------
Hannu


17 Jul 2002 11:33:37 +0200
Hannu Krosing <hannu@tm.ee>
Re: pg_views.definition
On Wed, 2002-07-17 at 09:56, Jan Wieck wrote:
> Joe Conway wrote:
> > The problem is that you would still need to keep a copy of your view
> > around to recreate it if you wanted to drop and recreate a table it
> > depends on. I really like the idea about keeping the original view
> > source handy in the system catalogs.
> 
> This has been the case all the time. I only see an attempt to
> make this impossible with the new dependency system. If I *must*
> specify CASCADE to drop an object, my view depends on, my view
> will be gone. If I don't CASCADE, I cannot drop the object.
> 
> So there is no way left to break the view temporarily (expert
> mode here, I know what I do so please let me)

I guess the real expert could manipulate pg_depends ;)

> and fix it later by just reparsing the views definition.

---------
Hannu


17 Jul 2002 11:36:52 +0200
Hannu Krosing <hannu@tm.ee>
Re: pg_views.definition