[GENERAL] alter table alter type CASCADE
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table alter type CASCADE
2010/5/5 Sim Zacks s...@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
Re: [GENERAL] alter table alter type CASCADE
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: thombr...@gmail.com Date: Wed, 5 May 2010 10:12:34 +0100 Subject: Re: [GENERAL] alter table alter type CASCADE To: s...@compulab.co.il CC: pgsql-general@postgresql.org 2010/5/5 Sim Zacks s...@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
Re: [GENERAL] alter table alter type CASCADE
On 5 May 2010 13:14, Chris Barnes compuguruchrisbar...@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
Re: [GENERAL] alter table alter type CASCADE
2010/5/5 Sim Zacks s...@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; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table alter type CASCADE
On Wed, May 5, 2010 at 7:31 AM, Thom Brown thombr...@gmail.com wrote: On 5 May 2010 13:14, Chris Barnes compuguruchrisbar...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
