Re: patch: Add columns via CREATE OR REPLACE VIEW

ALTER VIEW does not sound useful for me.
CREATE OR REPLACE VIEW should create or replace view and only thing that
should be same is the name of the view. It's up to Postgres to invalidate
all plans and up to developer to make sure that all places where his view is
used will stay still working. All this discussion about matching up columns
and AI seems totally useless to me :)

On Fri, Aug 8, 2008 at 4:41 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> > 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
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>