Thread: partitioned table + postgres_FDW not working in 9.3
Greetings, I've got two different 9.3 clusters setup, a & b (on Linux if that matters). On cluster b, I have a table (nppsmoke) that is partitioned by date (month), which uses a function which is called by a trigger to manage INSERTS (exactly as documented in the official documentation for partitioning of tables). I've setup a postgres foreign data wrapper server on cluster a which points to cluster b, and then setup a foreign table (nppsmoke) on cluster a which points to the actual partitioned (nppsmoke) table on cluster b. The partitions on cluster b use the naming scheme "nppsmoke_$YYYY_$MM" (where Y=4 digit year, and M=2 digit month). For example, the current month's partition is named nppsmoke_2013_09 . 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 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? thanks
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
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'.
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
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