Обсуждение: search_path versus dynamic CREATE SCHEMA
Hi folks, I am curious about why the following doesn't work as expected (tested on 9.0.3 and HEAD). CREATE OR REPLACE FUNCTION make_schema(_name text) RETURNS void LANGUAGE plpgsql VOLATILE AS $$ DECLARE _quoted text; BEGIN _quoted = quote_ident(_name); EXECUTE 'CREATE SCHEMA ' || _quoted; EXECUTE 'SET LOCAL search_path TO ' || _quoted; CREATE TABLE t (k int primary key); INSERT INTO t VALUES (1); RETURN; END; $$; SELECT make_schema('a'), make_schema('b'); I am expecting this script to create two new schemas called 'a' and 'b', each with its own table called 't' containing one row. This is what actually happens: CREATE FUNCTION psql:../test-dynamic-schema.sql:16: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CONTEXT: SQL statement "CREATE TABLE t (k int primary key)" PL/pgSQL function "make_schema" line 9 at SQL statement psql:../test-dynamic-schema.sql:16: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CONTEXT: SQL statement "CREATE TABLE t (k int primary key)" PL/pgSQL function "make_schema" line 9 at SQL statement psql:../test-dynamic-schema.sql:16: ERROR: duplicate key value violates unique constraint "t_pkey" DETAIL: Key (k)=(1) already exists. CONTEXT: SQL statement "INSERT INTO t VALUES (1)" PL/pgSQL function "make_schema" line 10 at SQL statement It seems that the first call to make_schema succeeds, but the second fails when it gets to the INSERT. The duplicate key complaint seems to suggest that the INSERT statement is resolving t as a.t, instead of the newly created b.t. But how is that possible? As far as I can see, the INSERT should be using the same search_path as the CREATE TABLE, which would have failed with "table already exists" if 'a' was at the front of the search_path, no? Cheers, BJ
Brendan Jurd <direvus@gmail.com> writes: > CREATE OR REPLACE FUNCTION make_schema(_name text) > RETURNS void LANGUAGE plpgsql VOLATILE AS $$ > DECLARE > _quoted text; > BEGIN > _quoted = quote_ident(_name); > EXECUTE 'CREATE SCHEMA ' || _quoted; > EXECUTE 'SET LOCAL search_path TO ' || _quoted; > CREATE TABLE t (k int primary key); > INSERT INTO t VALUES (1); > RETURN; > END; > $$; > It seems that the first call to make_schema succeeds, but the second > fails when it gets to the INSERT. The duplicate key complaint seems > to suggest that the INSERT statement is resolving t as a.t, instead of > the newly created b.t. But how is that possible? The CREATE TABLE is a utility statement, which has no plan to cache; but the INSERT is a plannable statement, so it caches a plan that references a.t. There has been debate before about whether or how to change that behavior ... regards, tom lane
On 1 June 2011 13:08, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Brendan Jurd <direvus@gmail.com> writes: >> It seems that the first call to make_schema succeeds, but the second >> fails when it gets to the INSERT. The duplicate key complaint seems >> to suggest that the INSERT statement is resolving t as a.t, instead of >> the newly created b.t. But how is that possible? > > The CREATE TABLE is a utility statement, which has no plan to cache; > but the INSERT is a plannable statement, so it caches a plan that > references a.t. There has been debate before about whether or how to > change that behavior ... > Ah, thanks for clearing that up. I hadn't thought about cached plans. I did a quick review of the previous discussions about this. For anyone who stumbles across this message later on, the bottom lines seem to be: 1) If you are in this situation, you are basically stuck with using EXECUTE for any plannable statements. 2) The winning suggestion for improving this seems to be to store (and lookup) cached plans on a per search_path setting basis, but as far as I know nobody has begun work on this. Cheers, BJ