Thread: 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