Re: patch: Add columns via CREATE OR REPLACE VIEW

> Well, my feeling is that if we are inventing a new feature we ought not
> paint ourselves into a corner by failing to consider what will happen
> when obvious extensions to the feature are attempted. Whether the
> present patch is self-consistent is not the question --- the question
> is do we have a self-consistent vision of how we will later do the
> other stuff like renaming, changing column type, etc.

If we can work out that design, I think that's great. However, it
doesn't actually 100% matter whether we know the one true way that we
will definitely implement those features - it only matters that none
of the things we might choose are inconsistent with what we're doing
now.

In order to avoid being AI-complete, REPLACE VIEW needs some kind of
straightforward algorithm for matching up the old and new target
lists. AFAICS, the only thing to decide here is what you want to use
as the key. There are three possibilities that I can think of: [1]
name, [2] position, [3] both name and position.

It's axiomatic that REPLACE VIEW can't be given the capability to make
any modification that involves changing the key field, so in [1] you
can't rename columns, in [2] you can't reorder columns, and in [3] you
can't do either. Furthermore, in [2], you also can't support dropping
columns, because a drop is indistinguishable from renaming and
retyping every column from the point of the drop onwards. Therefore,
the maximum set of operations REPLACE VIEW can potentially support in
each scenario are:

[1] add column, change type, drop column, reorder columns
[2] add column, change type, rename
[3] add column, change type, drop column

The actual set of operations supported may be less either because of
implementation limitations or because you don't want to provide users
with a foot-gun. ISTM that allowing REPLACE VIEW to do renames in
scenario [2] can be pretty much rejected outright as a violation of
the principle of least surprise - there is an enormous danger of
someone simultaneously renaming and retyping a whole series of columns
when they instead intended to drop a column. Similarly, in scenario
[1] or [3], ISTM that allowing someone to drop columns using REPLACE
VIEW is something of a foot-gun unless we are in scenario [1] and
reordering columns is also implemented, because users who don't RTFM
will try to reorder columns and it will succeed and fail erratically
according to whether there are dependencies that prevent dropping and
re-adding whatever subset of columns need to be shuffled to create the
same effect as would be produced by reordering. However, in any
scenario, I can't see how adding columns or changing column types is
likely to produce any confusion or user-unexpected behavior. Perhaps
I'm missing something?

Personally, I favor scenario [1]. I hardly ever rename database
columns, and I don't mind needing to ALTER VIEW RENAME COLUMN on those
rare occasions when I do, but I add new columns to my tables (which
then also need to be added to my views) on a regular basis. If I
could keep groups of related columns together in the table and view
definitions without having to drop and recreate the objects, that
would be awesome. But I'm not sure it's worth the amount of
implementation that would be required to get there, especially if all
of that implementation would need to be done by me (and
double-especially if none of it would likely be included in -core).

Of course, as I said before, nothing we do in REPLACE VIEW precludes
having a powerful implementation of ALTER VIEW. But I think the
coding to make ALTER VIEW do these operations is a lot trickier,
because you have to deal with modifying the query that's already in
place piecemeal as you make your changes to the view. It's not that
it can't be done, but I doubt it can be done in an 8K patch, and as
mentioned upthread, it certainly can't be done in a fully general
way... you will still frequently need to CREATE OR REPLACE VIEW
afterwards. To put that another way, ALTER TABLE is a complicated
beast because you have to worry about how you're going to handle the
existing data, and ALTER VIEW will be a complicated beast for the
analogous reason that you need to worry about handing the existing
rewrite rule. But at the moment when a REPLACE VIEW command is
executed, that problem goes away, because now you have the query in
your hand and just need to make the relation match it without breaking
any of the dependencies.

...Robert