Thread: View "Caching" - Is this Known and Expected Behavior?
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.
"David Johnston" <polobo@yahoo.com> writes:
> I am wondering whether the behavior I am observing is expected.
No, it isn't. Please provide a concrete test case.
regards, tom lane
On Tue, Aug 23, 2011 at 4:36 PM, David Johnston <polobo@yahoo.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
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, August 23, 2011 5:51 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] View "Caching" - Is this Known and Expected Behavior?
"David Johnston" <polobo@yahoo.com> writes:
> I am wondering whether the behavior I am observing is expected.
No, it isn't. Please provide a concrete test case.
regards, tom lane
-------------------------------------
OK, I found the true cause of the behavior; schemas. CREATE OR REPLACE only
looks at the first schema listed for a match; if one is not present it stops
searching and instead immediately chooses the CREATE option.
The following script results in two "testview" VIEWS; one in "test1" and one
in "public". With the specification of "OR REPLACE" it would make sense
that the entire search_path would be searched for an object to replace
BEFORE a new object is created in the first schema listed. Since changing
this behavior is likely to be difficult having a "REPLACE OBJECT" command
would make sense - one that errors if a matching object cannot be located.
In my situation my "CREATE OR REPLACE" made me a second VIEW which my direct
call used but the original VIEW was still being used by the two dependent
views. That said, the dependent VIEWS refer to the source view using an
unqualified name - so in theory they should have ended up using the newly
created VIEW as well.
Especially since "CREATE OR REPLACE" is often used interactively it would be
somewhat rational to emit a NOTICE indicating which option (CREATE |
REPLACE) was picked; and the resultant schema where the replacement was
performed. Regardless, depending on which option "search all schemas,
replace if not found OR search first listed schema, add if not present" is
chosen the relevant documentation sections should probably make it clear how
the system determines if "...a view of the same name already exists".
My $0.03
David J.
CREATE SCHEMA test1;
BEGIN;
SET LOCAL search_path = test1;
CREATE VIEW testview AS
SELECT 1 AS resultcol;
;
COMMIT;
BEGIN;
SET LOCAL search_path = public, test1;
CREATE OR REPLACE VIEW testview AS
SELECT 2 AS resultcol;
;
COMMIT;