ALTER TABLE should change respective views

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

Thanks a lot. I thought I would go with writing a function to Drop the views , ALTER table and the recreate the views so as to take care of the column type changes in the table.

--- On Tue, 5/5/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:

From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [HACKERS] ALTER TABLE should change respective views
To: "Peter Eisentraut" <peter_e@gmx.net>
Cc: pgsql-hackers@postgresql.org, "Archana Sundararam" <archnasr@yahoo.com>
Date: Tuesday, May 5, 2009, 8:10 AM

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

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