On Tue, Aug 23, 2011 at 4:36 PM, David Johnston <polobo@xxxxxxxxx> wrote: > 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. One thing that's very important to understand about views in postgres is that they are *mostly* like SQL macros. The view definition is simply injected into the outer query so that if you see this problem using dependent views, you should also see it in a fully expanded query since that is what postgres does under the hood. Note this is not necessarily true for other databases (and when it isn't, using views tends to suck). I say mostly, because there are a few changes postgres makes when parsing and storing the SQL behind views for later use. For example, "select * from foo" is expanded to "select foo.a, foo.b ... from foo" etc. A simple \d+ on the view should give you the sql as postgres sees it post storage. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
