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