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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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