Re: search_path versus dynamic CREATE SCHEMA

On 1 June 2011 13:08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Brendan Jurd <direvus(at)gmail(dot)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