partitioned table + postgres_FDW not working in 9.3

From: Lonni J Friedman <netllama(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: partitioned table + postgres_FDW not working in 9.3
Date: 2013-09-24 17:16:50
Message-ID: CAP=oouGibPvtGN=1KDugCUfKWOQ-PD6WFqT_jaAJntspALHBGw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rémi Cura 2013-09-24 17:18:31 Re: Howto import regularly some CSV files with variing names?
Previous Message Steve Crawford 2013-09-24 15:49:25 Re: Howto import regularly some CSV files with variing names?