Hey All,
I am wondering whether the behavior I am observing is expected. The rough scenario I have setup goes as follows (I can likely put together a test script if that is warranted):
version
PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
Initially:
VIEW inner := SELECT * FROM complex WHERE filter;
VIEW outer := SELECT * FROM inner JOIN other;
Now, I discover that the “filter” in the “inner” VIEW is wrong (had hard-coded a month/year combination during testing) and so I modified the WHERE clause of the “inner” VIEW. I do this using CREATE OR REPLACE VIEW inner […]
Now, I can (SELECT * FROM inner) and I get the expected results. However, if I (SELECT * FROM outer) the query (including the explain), shows me original “inner” plan and I thus get – in this case – no results (since the hard-coded date does not match my live data).
Since I did not change the signature of the VIEW the CREATE OR REPLACE worked as expected.
I have pretty good feel for how/why this is happening (though a precise explanation is welcomed), and obviously I will need to recreate the dependent VIEWs, but I am curious whether any efforts have/are being taken to avoid this issue in the future.
Thank you for your attention in this matter,
David J.
