partitioned table + postgres_FDW not working in 9.3

Hi Lonni,

2013/9/25 Lonni J Friedman <netllama@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.hanada@gmail.com> wrote:

Hi Lonni,

2013/9/25 Lonni J Friedman <netllama@gmail.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'.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Lonni J Friedman <netllama@gmail.com> writes:

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?

It's intentional.

Possibly more to the point, don't you think your trigger function is
rather fragile if it assumes the caller has provided a particular
search path setting?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Sep 26, 2013 at 8:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Lonni J Friedman <netllama@gmail.com> writes:

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?

It's intentional.

Possibly more to the point, don't you think your trigger function is
rather fragile if it assumes the caller has provided a particular
search path setting?

To be honest, I don't have much experience with functions, and was
using the trigger function from the official documentation:
http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general