Re: partitioned table + postgres_FDW not working in 9.3

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

Regards,
--
Shigeru HANADA