Thread: pg_views.definition
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
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, Pennsylvania19026
> 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
"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
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 #
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
objectstoo
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
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 #
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, Pennsylvania19026
> > 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
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
> > 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
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
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 #
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
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