Re: View "Caching" - Is this Known and Expected Behavior?

On Tue, Aug 23, 2011 at 4:36 PM, David Johnston <polobo(at)yahoo(dot)com> 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