Re: partitioned table + postgres_FDW not working in 9.3

Hi Shigeru,
Thanks for your reply. This sounds like a relatively simple
workaround, so I'll give it a try. Is the search_path of the remote
session that postgres_fdw forces considered to be intentional,
expected behavior, or is it a bug?

thanks!

On Wed, Sep 25, 2013 at 7:13 PM, Shigeru Hanada
<shigeru(dot)hanada(at)gmail(dot)com> wrote:
> Hi Lonni,
>
> 2013/9/25 Lonni J Friedman <netllama(at)gmail(dot)com>:
>> The problem that I'm experiencing is if I attempt to perform an INSERT
>> on the foreign nppsmoke table on cluster a, it fails claiming that the
>> table partition which should hold the data in the INSERT does not
>> exist:
>>
>> ERROR: relation "nppsmoke_2013_09" does not exist
>> CONTEXT: Remote SQL command: INSERT INTO public.nppsmoke(id,
>> date_created, last_update, build_type, current_status, info, cudacode,
>> gpu, subtest, os, osversion, arch, cl, dispvers, branch, pass, fail,
>> oldfail, newfail, failureslog, totdriver, ddcl, buildid, testdcmd,
>> pclog, filtercount, filterlog, error) VALUES ($1, $2, $3, $4, $5, $6,
>> $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
>> $21, $22, $23, $24, $25, $26, $27, $28)
>> PL/pgSQL function public.nppsmoke_insert_trigger() line 30 at SQL statement
>
> I could reproduce the problem.
>
>> If I run the same exact SQL INSERT on cluster b (not using the foreign
>> table), then it works. So whatever is going wrong seems to be related
>> to the foreign table. Initially I thought that perhaps the problem
>> was that I needed to create all of the partitions as foreign tables on
>> cluster a, but that doesn't help.
>>
>> Am I hitting some kind of foreign data wrapper limitation, or am I
>> doing something wrong?
>
> The cause of the problem is search_path setting of remote session.
> For some reasons, postgres_fdw forces the search_path on the remote
> side to be 'pg_catalog', so all objects used in the session
> established by postgres_fdw have to be schema-qualified. Trigger
> function is executed in such context, so you need to qualify all
> objects in your trigger function with schema name, like
> 'public.nppsmoke_2013_09'.