Re: search_path versus dynamic CREATE SCHEMA

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brendan Jurd <direvus(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: search_path versus dynamic CREATE SCHEMA
Date: 2011-06-01 03:08:55
Message-ID: 7288.1306897735@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Brendan Jurd <direvus(at)gmail(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2011-06-01 03:11:04 Re: Consistency of distributed transactions
Previous Message Craig Ringer 2011-06-01 02:26:45 Re: troubles with initdb