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

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;