Re: alter table alter column ... (larger type) ... when there are dependent views

On Tue, May 10, 2016 at 11:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> You should look at the code in ALTER TABLE that tries to rebuild index
> definitions during ALTER COLUMN TYPE, and see if that can be adapted
> to updating views.

I think the problems are almost entirely different. In the case of
ALTER TABLE, we just need to know whether the modified data will still
index in the same way. There are rules for that. In the case of
views, the problem has more to do with potential POLA violations.
What the user will want (I think) is for the dependent view to end up
in the same state that it would have ended up in if the CREATE VIEW
command had been issued after the ALTER TABLE command. But I'm pretty
sure we lack the information to do that in all cases.

We could try some hack, though. We could say that when you alter the
table (with some special option), the view definition gets modified by
inserting a cast. That is, if v.a gets changed from varchar(20) to
varchar(40), we rewrite the view definition so that wherever there was
previously a reference to v.a, it gets replaced with a reference to
(v.a::varchar(40)). That might lead to hideous results in the face of
multiple ALTER TABLE commands, though, and it's doubtful whether it is
really the behavior the user wants. What the user actually wants, I
think, is for the type of the view column to change from varchar(20)
to varchar(40) when the underlying table is altered. That, however,
seems like a Pandora's box. At least inserting casts would let the
ALTER TABLE succeed, and then you could fix the view afterward with
CREATE OR REPLACE VIEW.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company