Re: ERROR: cannot change name of view column

I appreciate the thoughtful feedback and appreciate your design perspective
of providing more safety to the DBA using the DDL.

I haven't seen this kind of safety in other DBs. I haven't done an
exhaustive review, however, I always had the impression it followed the
English meaning of the command.

Since CREATE ... OR REPLACE is literally: "if exists, drop and create,
else, create", I don't see any reason to have a safety. It not CREATE ...
OR ALTER SAFELY, same for DROP, there is no safety, the dependent views
will fail afterwards.

It's no problem for me, it was just a suggestion to bring the language
closer to it's definition and more intuative from my humble perspective.

Close this issue as your team feels best.

Kind regards,
Rèal

On Tue, Jan 1, 2019, 13:18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:

> =?UTF-8?Q?R=C3=A9al_A=2E_Carbonneau?= <contact(at)realcarbonneau(dot)com> writes:
> > Changing the name of any column in a view gives the error "ERROR: cannot
> > change name of view column".
>
> In CREATE OR REPLACE VIEW, you mean? Yeah, that's intentional.
>
> > Thus, the original error message is not completely correct. Ideally,
> > the CREATE OR REPLACE VIEW would automatically apply the ALTER TABLE ...
> > RENAME COLUMN (or underlying procedure) since it is available and
> fulfills
> > the original DDL request, thus making the error message unnecessary.
>
> DWIM doesn't seem like a good idea here to me. Doing what you suggest
> would be far more likely to let mistakes get by undetected than to
> actually do what the user intended. In many cases, the column types
> are all alike, so that the column names are the only cross-check there
> is that the new view definition matches up with the old.
>
> For example, suppose you originally wrote
>
> CREATE VIEW v AS SELECT x, y FROM ...
>
> and then you want to change something in the FROM clause, but you
> fat-finger the replacement command as
>
> CREATE OR REPLACE VIEW v AS SELECT x, z FROM ...
>
> If y and z are of the same datatype, this would pass undetected
> if we try to be "helpful" in this way.
>
> Another class of examples involves accidentally switching the order of
> view output columns. This would interact especially badly with the
> fact that we do allow CREATE OR REPLACE VIEW to append columns.
> If you should have written
>
> CREATE OR REPLACE VIEW v AS SELECT x, y, z FROM ...
>
> to add "z" to the original list, but get the order wrong, this proposal
> would allow very serious breakage of existing view users to occur.
> (Previously-stored views will refer to those columns by number, not name,
> which is really necessary to allow ALTER VIEW RENAME COLUMN to work at
> all.)
>
> > Or at the very least, the error message could be changed to suggest using
> > the ALTER TABLE ... RENAME COLUMN until the feature is included in the
> > CREATE OR REPLACE VIEW.
>
> Perhaps there's room for a "HINT: if you intended to change view
> column names, use ALTER VIEW RENAME COLUMN". But I suspect such
> a hint would get printed in many cases where it didn't apply,
> making it perhaps more confusing not less.
>
> regards, tom lane
>