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

On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

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

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