alter table alter type CASCADE

2010/5/5 Sim Zacks <sim@compulab.co.il>

One of the biggest problems I have maintaining a database with a lot of
views is that when I want to change a datatype, I have to drop every
view uses the column and every view that uses those views etc...
This turns into a maintenance nightmare.

Is there any intention of adding a CASCADE to alter type which would
automatically update any dependencies with the new datatype? Obviously
it should error out if it wouldn't have let you save one of the views
with the new datatype.

Thanks
Sim

--

Yes, there is an intention of adding such functionality (3rd item) but
probably not very straightforward:
http://wiki.postgresql.org/wiki/Todo#Views_.2F_Rules

Regards

Thom

It has been some years since I worked with Oracle, doesn't Oracle recompile the view when the object it references changes in structure?

Send

From: thombrown@gmail.com
Date: Wed, 5 May 2010 10:12:34 +0100
Subject: Re: [GENERAL] alter table alter type CASCADE
To: sim@compulab.co.il
CC: pgsql-general@postgresql.org

2010/5/5 Sim Zacks <sim@compulab.co.il>

One of the biggest problems I have maintaining a database with a lot of

views is that when I want to change a datatype, I have to drop every

view uses the column and every view that uses those views etc...

This turns into a maintenance nightmare.

Is there any intention of adding a CASCADE to alter type which would

automatically update any dependencies with the new datatype? Obviously

it should error out if it wouldn't have let you save one of the views

with the new datatype.

Thanks

Sim

--
Yes, there is an intention of adding such functionality (3rd item) but probably not very straightforward: http://wiki.postgresql.org/wiki/Todo#Views_.2F_Rules

Regards

Thom

_________________________________________________________________
Win a $10,000 shopping spree from Hotmail! Enter now.
http://go.microsoft.com/?linkid=9729711

On 5 May 2010 13:14, Chris Barnes <compuguruchrisbarnes@hotmail.com> wrote:

It has been some years since I worked with Oracle, doesn't Oracle recompile
the view when the object it references changes in structure?
Send

What does Oracle do when you've got a view like:

CREATE OR REPLACE VIEW test_view AS
SELECT test.test_id, test.test_value, date_is_future(test.test_date) as
upcoming
FROM test;

Where date_is_future expects a text field, but you change the test table so
that test_date is now varchar(20)? The function no longer matches the
signature. Does it just prevent it? And what about when conditions are
provided in a WHERE clause which become invalid when the column type
changes?

Thom

2010/5/5 Sim Zacks <sim@compulab.co.il>:

One of the biggest problems I have maintaining a database with a lot of
views is that when I want to change a datatype, I have to drop every
view uses the column and every view that uses those views etc...
This turns into a maintenance nightmare.

Then I would question your approach to maintenance. In the past when
I've had to deal with this type of thing, all views were created from
a script. Edit the script, run the script, you're done. Do it in a
transaction and if there are any errors nothing changes. Script looks
something like:

begin;
drop view x1;
drop view y1;
... more drop statements
create view x1...
create view y1 ...
commit;

On Wed, May 5, 2010 at 7:31 AM, Thom Brown <thombrown@gmail.com> wrote:

On 5 May 2010 13:14, Chris Barnes <compuguruchrisbarnes@hotmail.com> wrote:

It has been some years since I worked with Oracle, doesn't Oracle
recompile the view when the object it references changes in structure?
Send

What does Oracle do when you've got a view like:

CREATE OR REPLACE VIEW test_view AS
 SELECT test.test_id, test.test_value, date_is_future(test.test_date) as
upcoming
   FROM test;

Where date_is_future expects a text field, but you change the test table so
that test_date is now varchar(20)?  The function no longer matches the
signature.  Does it just prevent it?  And what about when conditions are
provided in a WHERE clause which become invalid when the column type
changes?

If Oracle can find a function that will make the view valid it will
continue to allow usage of the view; though it is still marked as
needing to be rebuilt from a user perspective any calls to it will
succeed if there is a possible way for the view to still be valid. If
there is no possible way for any use of the view to succeed then the
calls fail.

--
Peter Hunsberger