> 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers