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