[GENERAL] Keeping sources of views, and tracking invalid objects (views) similar to oracle

Поиск
Список
Период
Сортировка
От bb ddd
Тема [GENERAL] Keeping sources of views, and tracking invalid objects (views) similar to oracle
Дата
Msg-id 815276382.4036583.1495200618457.JavaMail.apache@nm63.abv.bg
обсуждение исходный текст
Список pgsql-general
Hello,

This idea has been raised more or less before as well as the problems it would solve. These are old, but IMHO the
problemremains: 

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.6070706@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'sapproach 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
severallevels 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
useviews. 

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
thatit is a bad thing, but these sources are now logically a part of the database definition. You just need them. In
oracleyou 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
oraclethese invalid objects are tracked, reported and recompiled if needed. In PG these invalid objects are the
mentionedabove 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
internallyPG 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
recompiledand it will start using the new column and return null, null, null all of a sudden. So which was the expected
behaviornow??? 

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
isin 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
thisto 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
keepworking 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
thesame 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
cannotbelieve 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
 recompileall views that depend on something? Granted with time it needs to happen less and less often because major
changesafter some point are not that needed. But especially when developing the views initially you often have to
changecolumn names, column order, all kind of things until you figure out the best organization of the views you need.
Itis a nightmare. 

Thanks



В списке pgsql-general по дате отправления:

Предыдущее
От: "Karl O. Pinc"
Дата:
Сообщение: Re: [GENERAL] Serializable isolation -- are predicate locks stillheld across all databases?
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Weird periodical pg log