Re: Possible enhancement : replace view ? - Mailing list pgsql-hackers

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