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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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.

Not sure I buy that argument; we really only try to make a similar index
on the new column. An example is that you can use ALTER COLUMN TYPE
to change a text column to timestamp, and if there's an index on the
column it just gets replaced by one using timestamp_ops, never mind that
the sort order and equality rules will be substantially different.

> In the case of
> views, the problem has more to do with potential POLA violations.

Indeed, but that's true for indexes as well.

> ... 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.

Well, for one thing it would require recursive updates of indirectly
dependent views. I think that's perfectly do-able, if you have something
that does what you want on the directly dependent view in the first place.
But it certainly raises the stakes in terms of the amount of damage an
ill-considered ALTER could do.

In any case, it would be a good idea to try to sketch out a spec for
the behavior you want before any code gets written.

regards, tom lane