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

On Tuesday, June 3, 2014, Robert Haas [via PostgreSQL] <
ml-node+s1045698n5805857h91(at)n5(dot)nabble(dot)com> wrote:

> On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5805857&i=0>> 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?
>
>
The current mechanism is DROP VIEWs -> ALTER TABLE -> CREATE VIEWs

The UI would prompt the user for the desired ALTER TABLE
parameters, calculate the DROP/CREATE commands, then issue all three sets
as a single transaction.

Having a more surgical REWRITE RULE command to alter a view without
dropping it may provide for performance improvements but, conceptually, the
current mechanism should be sufficient to allow for this tool to be
developed.

The main thing that core could do to help is to store as text of the
original create view command - though it may be sufficient to reverse
engineer from the rule. Having both available would give any tools more
options.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-create-dependent-views-on-ALTER-TABLE-ALTER-COLUMN-TYPE-tp5804972p5805864.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.