Skip site navigation (1) Skip section navigation (2)

search_path versus dynamic CREATE SCHEMA

From: Brendan Jurd <direvus(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: search_path versus dynamic CREATE SCHEMA
Date: 2011-06-01 02:25:02
Message-ID: BANLkTimaiyrVba7=NaYQCsvasiJBLe2gHg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Craig RingerDate: 2011-06-01 02:26:45
Subject: Re: troubles with initdb
Previous:From: Craig RingerDate: 2011-06-01 01:29:39
Subject: Re: Consistency of distributed transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group