Search Postgresql Archives

Re: partitioned table + postgres_FDW not working in 9.3

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
> Hi Lonni,
>
> 2013/9/25 Lonni J Friedman <netllama@xxxxxxxxx>:
>> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux