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
