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

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