Thread: Possible enhancement : replace view ?

Possible enhancement : replace view ?

From
Emmanuel Charpentier
Date:
Dear all,

The current implementation of views uses OIDs, not table/view names. As 
a consequence, when you happen to replace (drop then create) an 
underlying table or view, you also have to drop and recreate all views 
using this table|view (and this recursively, of course ...).

I stumbled on this while doing repeat analyses (involving repeated uses 
of aggregation) of the same dataset using slight variations of the 
subset of interest. When my dataset was small, I used to do that in 
(yuck !) MS-Access by creating a view defining the subset of interest, 
then creating views based on this view, and so on... Now that my dataset  is too large to be Access-manageable, I
migratedit to PostgreSQL 
 
(which, BTW, gave me nice performance enhancements), but I had to change  my working habits. I have now to create a
scriptdefining my views, 
 
then to run it at each and every variation of the subset of interest ... 
To be able to conserve existing views would definitely be a bonus.

Of course, the overhead is necessary to handle the general case. 
However, there is a special case where this is unnecessary : when the 
new table or view "class" definition is a (possibly improper) subclass 
of the original one, or, if you prefer, when the column set of the new 
definition is a (possibly improper) superset of the old one.

For tables, this case is already handled by a judicious use of alter 
table, at least in its present form (adding DROP COLUMN, which might be 
an interesting feature for other reasons, entails the risk of 
invalidating existing views ...). However, there is currently no easily 
reachable way to do that for a view (I suppose that the special case of 
modifying the definition of a view creating the same columns in the old 
and new definitions might be possible with a "clever" (ab)use of system 
catalogs, but I tend to be *very* wary of such hacks ...).

Of course, I am aware that view definitions aren't just stored, but that  a lot of rewriting is involved before storing
theactual execution 
 
plan.    Modifying a view definition would entail re-processing of other 
view definitions. But so is the case with the modification of a table ...

What do you think ?

-- 
Emmanuel Charpentier



Re: Possible enhancement : replace view ?

From
Hannu Krosing
Date:
On Wed, 2002-08-14 at 04:08, Emmanuel Charpentier wrote:
> Dear all,
> 
...

> Of course, I am aware that view definitions aren't just stored, but that 
>   a lot of rewriting is involved before storing the actual execution 
> plan.    Modifying a view definition would entail re-processing of other 
> view definitions. But so is the case with the modification of a table ...
> 
> What do you think ?

I'm trying to propose a scenario where

1. The SELECT clause defining the view is preserved

2. DROP of undrlying table/column will _not_ drop the view, but just
mark it dirty

3. Using the view checks for the dirty flag and if it is set tries to
recreate the view from its plaintext definition.

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



Re: Possible enhancement : replace view ?

From
Tom Lane
Date:
Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
> What do you think ?

I think Gavin Sherry is already working on this.
        regards, tom lane


Re: Possible enhancement : replace view ?

From
Emmanuel Charpentier
Date:
Hannu Krosing wrote:
> On Wed, 2002-08-14 at 04:08, Emmanuel Charpentier wrote:
> 
>>Dear all,
>>
> 
> ...
> 
> 
>>Of course, I am aware that view definitions aren't just stored, but that 
>>  a lot of rewriting is involved before storing the actual execution 
>>plan.    Modifying a view definition would entail re-processing of other 
>>view definitions. But so is the case with the modification of a table ...
>>
>>What do you think ?
> 
> 
> I'm trying to propose a scenario where
> 
> 1. The SELECT clause defining the view is preserved
> 
> 2. DROP of undrlying table/column will _not_ drop the view, but just
> mark it dirty
> 
> 3. Using the view checks for the dirty flag and if it is set tries to
> recreate the view from its plaintext definition.

I might be dense, but why not try to recreate it directly after the 
table/column modification ?

> ---------------
> Hannu
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html



Re: Possible enhancement : replace view ?

From
Gavin Sherry
Date:
Tom,

I submitted a patch for this a few days ago. Did it not hit pgsql-patches?

Gavin

On Tue, 13 Aug 2002, Tom Lane wrote:

> Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
> > What do you think ?
> 
> I think Gavin Sherry is already working on this.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 



Re: Possible enhancement : replace view ?

From
Hannu Krosing
Date:
On Wed, 2002-08-14 at 04:23, Emmanuel Charpentier wrote:
> Hannu Krosing wrote:
> > 
> > I'm trying to propose a scenario where
> > 
> > 1. The SELECT clause defining the view is preserved
> > 
> > 2. DROP of undrlying table/column will _not_ drop the view, but just
> > mark it dirty
> > 
> > 3. Using the view checks for the dirty flag and if it is set tries to
> > recreate the view from its plaintext definition.
> 
> I might be dense, but why not try to recreate it directly after the 
> table/column modification ?

If it is a DROP TABLE/CREATE TABLE sequence you have no idea that you
have to recreate a view.

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


Re: Possible enhancement : replace view ?

From
Emmanuel Charpentier
Date:

Hannu Krosing wrote:
> On Wed, 2002-08-14 at 04:23, Emmanuel Charpentier wrote:
> 
>>Hannu Krosing wrote:
>>
>>>I'm trying to propose a scenario where
>>>
>>>1. The SELECT clause defining the view is preserved
>>>
>>>2. DROP of undrlying table/column will _not_ drop the view, but just
>>>mark it dirty
>>>
>>>3. Using the view checks for the dirty flag and if it is set tries to
>>>recreate the view from its plaintext definition.
>>
>>I might be dense, but why not try to recreate it directly after the 
>>table/column modification ?
> 
> 
> If it is a DROP TABLE/CREATE TABLE sequence you have no idea that you
> have to recreate a view.

Right. But I was wary of delaying recreation : views are more often than 
not created by programmers/DBAs/someone somewhat competent in DB design 
and use, in order to be used by people not necessarily aware of the real 
struxture of data (that's the whole point of having views, BTW). 
Delaying recreation entails the risk of overlooking a problem and 
getting a nice phone call  at 2 AM from the maintainance guy stuttering 
that he can no longer access its (vital, of course) data ...

Tradeoffs, again ... What about emitting warnings after table drop 
(easy)/creation (not so easy !) ?

BTW : since drop column and alter various attributes (not null, primary 
key, etc ...) will be possible, shoudn't the need to drop/recteate a 
table drastically decrease ?

E. g. : I recently created a log table wit a field date timestamptz 
default now(), only to discover that, due to current limitations of the 
ODBC driver, I should have used timestamptz[0] (ODBC doesn't like 
fraction of seconds in datetime). I kludged away bby updating (set 
date=date_trunc('second',date)) and altering default to 
date_trunc('second',date) (Yuck !), but the real solution would have 
been of course to recreate the column with the right attribute, which 
currently involves dropping/recreating the table, therefore losing all 
defined views.

What a ten-thumbs programmer such as me would love to see in such a 
scenario would be something along the lines of :

# Create table T (date as timestamp defailt now(), ...) ...;
CREATE
# Create view X as select date, ... from T join ...;
CREATE
# Create view Y as select <anthing but date> ... from T where ...;
CREATE
Create view Z as select date, ... from T join ...;
# CREATE
Create view U as select ... from Z left outer join ...;
---
--- Insert data here
---
...
---
--- Later ! Insert ODBC epiphany  here
---
# alter table T add column newdate timestamptz[0];
ALTER --- I can't remember the exact acknowledgement sent for alter column
update T set newdate=date;
UPDATE (somenumber) 0
alter table T rename column date to olddate;
ALTER --- ditto
WARNING : View X might have become invalid. Please check it or drop it !
WARNING : View Z might have become invalid. Please check it or drop it !
WARNING : View U might have become invalid. Please check it or drop it !
alter table T rename newdate to date;
ALTER --- ditto;
WARNING : View X successfully recreated from it's original SQL 
definition. Please check it or drop it !
WARNING : View Z successfully recreated from it's original SQL 
definition. Please check it or drop it !
WARNING : View U successfully recreated from it's original SQL 
definition. Please check it or drop it !
Alter table T drop column olddate;
ALTER

Exercise left for the reader : what about inheritance ?
Another exercise : what about adding/dropping indices (indexes ?) ?

Your thoughs ?

__
Emmanuel Charpentier



Re: Possible enhancement : replace view ?

From
Rod Taylor
Date:
> Tradeoffs, again ... What about emitting warnings after table drop 
> (easy)/creation (not so easy !) ?

The warnings are certainly there now.  Dependency code won't let you do
such a thing without specifying CASCADE.

Hopefully CREATE OR REPLACE VIEW will be applied soon, which solves part
two of the problem.



Re: Possible enhancement : replace view ?

From
Tom Lane
Date:
Gavin Sherry <swm@linuxworld.com.au> writes:
> I submitted a patch for this a few days ago. Did it not hit pgsql-patches?

It did ... I had some gripes about it ... I thought you were working on
the gripes?
        regards, tom lane


Re: Possible enhancement : replace view ?

From
Bruce Momjian
Date:
Ah, that's why it wasn't in my mailbox.  Gavin,

http://groups.google.com/groups?hl=en&lr=&ie=ISO-8859-1&q=replace+view+gavin&btnG=Google+Search&meta=group%3Dcomp.databases.postgresql.*


Gavin Sherry <swm@linuxworld.com.au> writes:
> It passes all regression tests. There's only one really sketchy part of
> the patch: UpdateAttributeTuples(). This routine is fairly dangerous since
> it simply removes a given relid's pg_attribute entries and creates a new
> set basic on a given TupleDesc. Naturally, it is only useful for views.

You can NOT allow CREATE OR REPLACE VIEW to change the tupledesc of the
view, so I stopped reading right here --- take it out and install
prevention instead.  Why do you think that REPLACE VIEW is interesting?
It's so you can modify a view without breaking things that depend on it
... and things that depend on it depend on the tupledesc.  This is
exactly analogous to not allowing REPLACE FUNCTION to change the return
type of the function.
                       regards, tom lane
---------------------------------------------------------------------------

Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> > I submitted a patch for this a few days ago. Did it not hit pgsql-patches?
> 
> It did ... I had some gripes about it ... I thought you were working on
> the gripes?
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073