On Tuesday, June 3, 2014, Robert Haas [via PostgreSQL] <
[hidden email]> wrote:
On Mon, Jun 2, 2014 at 10:00 PM, Tom Lane <[hidden email]> 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.
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.
View this message in context:
Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE? Sent from the
PostgreSQL - hackers mailing list archive at Nabble.com.
