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

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, August 23, 2011 5:51 PM
To: David Johnston
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] View "Caching" - Is this Known and Expected Behavior?

"David Johnston" <polobo(at)yahoo(dot)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;