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 2:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think deparse-and-reparse is exactly what we have to do, mainly because,
>> if you subscribe to the idea that the user should see and approve semantic
>> changes, what else are we going to show her except SQL? If she wants to
>> adjust the changes, it's even less plausible that the working
>> representation is not SQL text. We might well produce the initial draft
>> form by manipulating the parsed querytree before deparsing, though.

> So I think the scenario we're talking about, simplified down to
> basics, is something like this:

> CREATE TABLE foo (a int);
> CREATE VIEW bar AS SELECT a FROM foo;
> ALTER TABLE foo ALTER COLUMN a SET DATA TYPE bigint;

> If we wanted to make that last statement succeed instead of failing,
> what would we want it to do?

My argument is that that command sequence, if issued exactly like that,
SHOULD fail. It is not the backend's task to fix this case, and any
smarts you try to put into ALTER TABLE to make it work are certain
to do the wrong thing a distressingly high percentage of the time.
Rather, it should be possible to build a client-side tool that can help
users with such changes.

> I can see two answers. Answer #1 is
> that the column type of bar.a changes from int to bigint and the view
> definition is still SELECT a FROM foo. In that case, showing the user
> the SQL does not help them see and approve semantic changes because
> the SQL is completely unchanged.

Yeah, we need some way of highlighting the semantic differences, and just
printing ruleutils.c output doesn't do that. But if the user is going to
put in a change to whatever choice the tool makes by default here,
I would expect that change to consist of adding (or removing) an explicit
cast in the SQL-text view definition. We can't make people learn some
random non-SQL notation for this.

Perhaps the displayed output of the tool could look something like

CREATE VIEW bar AS
SELECT
a -- this view output column will now be of type int8 not int4
FROM foo;

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.

regards, tom lane