Hello, This idea has been raised more or less before as well as the problems it would solve. These are old, but IMHO the problem remains:
https://www.postgresql.org/message-id/24293.1272638299%40sss.pgh.pa.us https://www.postgresql.org/message-id/D86CC5D8-C65A-4196-BB94-91614A814D29%40gtwm.co.uk https://www.postgresql.org/message-id/4bd1c66e.6070...@comcast.net https://www.postgresql.org/message-id/CAAQkdDod-N6nPbCKZ12zxjmYND%3D8tak3cZyJN40hELEbEfcw0A%40mail.gmail.com But has been more or less discarded as "it's completely against the system structure at a number of levels" and "Oracle's approach is bad" First of all I care mostly about views. These are extremely important and working with them in PG is a pain. We have several levels of view dependencies. In addition to them being important in general, they are twice more important in PG where CTE's are optimization boundaries (for whatever reason) and if you want a decent plan and clean and easy to read short queries you just have to use views. Want to make some points why I don't agree what has been previously said on this topic. Here is what I don't agree with (can be found in above links): "That has some advantages; for example, you can rename a column in some other table that the view uses, and nothing breaks" - Robert Haas "IMO, the way Oracle does this pretty much sucks, and shouldn't be emulated. If they know how to recompile the view, why don't they just do it? What you describe is about as user-unfriendly as it gets." - Tom Lane First of all how I look at this whole thing: 1. PG, not keeping the sources of the views, forces developers to maintain the sources externally (SVN, whatever). Not that it is a bad thing, but these sources are now logically a part of the database definition. You just need them. In oracle you are not forced to keep sources externally. 2. Given 1, In both PG and Oracle you HAVE invalid objects when you change the name of a table's column for example. In oracle these invalid objects are tracked, reported and recompiled if needed. In PG these invalid objects are the mentioned above sources that are kept externally. To make 2. more clear. After say: CREATE TABLE ttt(i NUMERIC); INSERT INTO ttt (VALUES (1),(2),(3)); CREATE OR REPLACE VIEW v_ttt AS SELECT i FROM ttt; SELECT * FROM v_ttt; -- returns 1,2,3 -- then : ALTER TABLE ttt RENAME COLUMN i to s; ALTER TABLE ttt add COLUMN i NUMERIC; SELECT * FROM v_ttt; -- still returns 1,2,3 -- This according to Haas is a feature. In my book this is bad. Because the actual definition of the view is not what internally PG parsed and stored. The actual, important to the developers, definition of the view is stored externally in an .sql file in SVN -- So next time a developer opens this file, fixes a bug in this view, or improves it or something. It will be recompiled and it will start using the new column and return null, null, null all of a sudden. So which was the expected behavior now??? About the second quote by Tom Lane: If memory serves, they recompile the views, but not automatically. First time this view is about to be executed, if it is in an invalid state, the source code that is kept internally will be used to recompile it. Which will propagate to recompiling all other invalid objects it depends on if any. If this is successful, all works fine. If not throws an error. Still the developer has an option, assuming he knows what he is doing, to not wait for this to happen, but ask the db for all invalid objects, and try recompile them himself. Again this cannot happen in PG, because the actual definition of the same invalid objects (which actually in some cases keep working because of that "feature") live outside the database and pg has no idea how they look. I understand this is one way to think of it. The PG way is that a table's column might change its name, but it is still the same column. But this is plain wrong. No database developer would ever think of it like this. And I don't think above scenario of renaming column and then adding a new one with the old name is uncommon. This whole thing I wrote is just to get to my main point: If view sources is preserved together with the parsed version (for performance reasons). You can: * change view definition any way you can without having to drop all 100 dependent views and recreating them again. * This renaming thing will be more sane. Please if somebody actually ever relied on this "feature", prove me wrong. I cannot believe a developer would ever benefit from such behavior. * For lazy people with simpler smaller database in one man projects, might not have to keep sources externally Am I the only one that has problems with working with a lot of views? How do you solve these problems? Always drop and recompile all views that depend on something? Granted with time it needs to happen less and less often because major changes after some point are not that needed. But especially when developing the views initially you often have to change column names, column order, all kind of things until you figure out the best organization of the views you need. It is a nightmare. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
