Thread: ALTER TABLE should change respective views
| hi, I have many views dependent on a table. So whenever I do alter table and change the column type I have to drop and recreate all the views. Is there any other easy way to propagate the changes in the table to the views. Any suggestion is welcome. Thanks, Archana |
On Monday 04 May 2009 23:11:22 Archana Sundararam wrote: > I have many views dependent on a table. So whenever I do alter table and > change the column type I have to drop and recreate all the views. Is there > any other easy way to propagate the changes in the table to the views. Any > suggestion is welcome. Consider this example: CREATE TABLE tab1 ( a int, b text ); CREATE VIEW view1 AS SELECT a, foo(b) FROM tab1; ALTER TABLE tab1 ALTER COLUMN b TYPE inet; Now what do expect should become of the view? CREATE VIEW view1 AS SELECT a, foo(b) FROM tab1; -- now using foo(inet) or CREATE VIEW view1 AS SELECT a, foo(b::text) FROM tab1; -- still using foo(text) (This becomes more entertaining if you specified a conversion function (USING) for the type change.) And this could then also change the return type of foo(), thus changing the row type of the view and would thus propogate up to other views. And so if you have "many views", as you say, this could become a great mess. You could probably define and implement a solution, but it would be very confusing and risky to use.
Peter Eisentraut <peter_e@gmx.net> writes:
> And this could then also change the return type of foo(), thus changing the
> row type of the view and would thus propogate up to other views. And so if
> you have "many views", as you say, this could become a great mess. You could
> probably define and implement a solution, but it would be very confusing and
> risky to use.
The SQL committee has also historically chosen to punt on such things.
Note the long-established rule that "*" is expanded at view definition
time (so adding columns doesn't change views). I also see a flat
prohibition on *any* view reference in the newly added SET DATA TYPE
command (SQL:2008 11.17 <alter column data type clause>):
7) C shall not be referenced in the <query expression> of any view descriptor.
regards, tom lane
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Thanks a lot. I thought I wouldgo with writing a function to Drop the views , ALTER table and the recreate the views so as to take care of the columntype changes in the table.<br /><br />--- On <b>Tue, 5/5/09, Tom Lane <i><tgl@sss.pgh.pa.us></i></b> wrote:<br/><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br />From:Tom Lane <tgl@sss.pgh.pa.us><br />Subject: Re: [HACKERS] ALTER TABLE should change respective views<br />To:"Peter Eisentraut" <peter_e@gmx.net><br />Cc: pgsql-hackers@postgresql.org, "Archana Sundararam" <archnasr@yahoo.com><br/>Date: Tuesday, May 5, 2009, 8:10 AM<br /><br /><div class="plainMail">Peter Eisentraut <<ahref="/mc/compose?to=peter_e@gmx.net" ymailto="mailto:peter_e@gmx.net">peter_e@gmx.net</a>> writes:<br />> Andthis could then also change the return type of foo(), thus changing the <br />> row type of the view and would thuspropogate up to other views. And so if <br />> you have "many views", as you say, this could become a great mess. You could<br />> probably define and implement a solution, but it would be very confusing and <br />> risky touse.<br /><br />The SQL committee has also historically chosen to punt on such things.<br />Note the long-established rulethat "*" is expanded at view definition<br />time (so adding columns doesn't change views). I also see a flat<br />prohibitionon *any* view reference in the newly added SET DATA TYPE<br />command (SQL:2008 11.17 <alter column datatype clause>):<br /><br />7) C shall not be referenced in the <query expression> of any view descriptor. <br/><br /> regards, tom lane<br /></div></blockquote></td></tr></table><br />
All, I was discussing this with a client who experiences this problem on a weekly basis, and the issue is mainly one of change management. That is, manually dropping all of the views & functions dependant on a table, changing the table, and recreating the views and functions, is a major PITA and substantially inhibits the use of views and functions for security and database abstraction. Add OID invalidation for cached plans into this and you have a bunch of developers taking their business logic out of the database and putting it into middleware. What would solve the issue for 90% of our users would be an ALTER TABLE ... CASCADE which would apply the changes to the table, and do a REPLACE VIEW and REPLACE FUNCTION for every dependant view and function, failing and rolling back if any REPLACE doesn't work automatically. Incompatible table changes would still require manual drop and recreation, of course. But most table changes to a production database are adding fields or changing constraints, which in most cases won't break dependant views or functions. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> Incompatible table changes would still require manual drop and
> recreation, of course. But most table changes to a production database
> are adding fields or changing constraints, which in most cases won't
> break dependant views or functions.
... as indeed they don't. What's your point? The question here was
about whether the DB should try to guess the right behavior for a
datatype change in an existing column.
regards, tom lane
On Tue, May 5, 2009 at 2:17 PM, Josh Berkus <josh@agliodbs.com> wrote: > Incompatible table changes would still require manual drop and recreation, > of course. But most table changes to a production database are adding > fields or changing constraints, which in most cases won't break dependant > views or functions. You can already add a column to a table or change a constraint without needing to drop and recreate dependent views or functions. You can also rename and drop columns. I think the real issue is when you have dependencies on a VIEW. http://archives.postgresql.org/pgsql-hackers/2008-05/msg00691.php I currently handle the problem you're describing by having a series of scripts which automatically drop pretty much every view in the database in reverse order of creation, and then recreate them all (all within a single transaction). I run it every time I do a release and it works great, but it's definitely not ideal, and wouldn't work at all but for the fact that my system is sufficiently lightly loaded that taking locks on all of those views is actually possible. 8.4 will be slightly better than 8.3 in that it will allow CREATE OR REPLACE VIEW to add additional columns to the end of the view definition. http://archives.postgresql.org/pgsql-committers/2008-12/msg00066.php ...but this still falls considerably short of where I'd like to be. The next logical step would probably be to support ALTER VIEW DROP COLUMN, but I haven't really looked at what would be required to implement that. Checking the dependencies is probably the easy part; the tricky things, I think, are (a) currently, attisdropped can never be set for any column of a view, does anything break if we change this? and (b) how do we modify the stored view definition to remove the dropped column from the query's target list? ...Robert