-----Original Message----- From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] Sent: Tuesday, August 23, 2011 5:51 PM To: David Johnston Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: View "Caching" - Is this Known and Expected Behavior? "David Johnston" <polobo@xxxxxxxxx> 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; -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
