Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Or something else; I don't claim to be a good UI designer. But in the
>> end, this is 90% a UI problem, and that means that raw SQL is seriously
>> poorly suited to solve it directly.

> I guess I don't agree that is 90% a UI problem. There's currently no
> mechanism whatsoever by means of which a user can change the data type
> of a column upon which a view depends.

Sure there is: I already illustrated it. You can temporarily set the
view to some dummy definition that doesn't reference the target table,
then do the ALTER COLUMN TYPE, then redefine the view the way you want.
Wrap it up in a transaction, and it's even transparent.

Now, what that doesn't do is let you change the output column type(s)
of the view, but I'd argue that entirely independently of this problem
it'd be reasonable for CREATE OR REPLACE VIEW to allow changing a column
type if the view is unreferenced (ie, basically the same conditions under
which a table column type can be changed today).

If you want to argue that this is unnecessarily complex, you can do so,
but claiming that it's not possible is simply false. I stand by the point
that what we lack is a sane UI for helping in complex cases --- and
nothing done behind-the-scenes in ALTER TABLE is going to qualify as
a sane UI. The complexity in this approach would be easily hidden in
a support tool, which will have much bigger problems to solve than whether
its eventual command to the backend requires multiple SQL steps.

> If we had such a mechanism,
> then perhaps someone could build a UI providing the sort of user
> feedback you're suggesting to help them use it more safely. But isn't
> the core server support the first thing?

I'm guessing you did not read
http://www.postgresql.org/message-id/18723.1401734537@sss.pgh.pa.us

regards, tom lane