Tuple-routing for certain partitioned tables not working as expected

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Tuple-routing for certain partitioned tables not working as expected
Date: 2017-08-07 03:45:35
Message-ID: bc3db4c1-1693-3b8a-559f-33ad2b50b7ad@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I noticed that tuple-routing for partitioned tables that contain
non-writable foreign partitions doesn't work as expected. Here is an
example:

postgres=# create extension file_fdw;
postgres=# create server file_server foreign data wrapper file_fdw;
postgres=# create user mapping for CURRENT_USER server file_server;
postgres=# create table p (a int) partition by list (a);
postgres=# create foreign table t1 partition of p for values in (1)
server file_server options (format 'csv', filename '/path/to/file',
delimiter ',');
postgres=# create table t2 partition of p for values in (2);
postgres=# insert into p values (1);
ERROR: cannot insert into foreign table "t1"

Looks good, but:

postgres=# insert into p values (2);
ERROR: cannot insert into foreign table "t1"

The insert should work but doesn't. (It also seems odd to me that the
error message points to t1, not t2.) The reason for that is
CheckValidResultRel in ExecSetupPartitionTupleRouting aborts any insert
into a partitioned table in the case where the partitioned table
contains at least one foreign partition into which the FDW can't insert.
I don't think that that is intentional behavior, so I'd like to
propose to fix that by skipping CheckValidResultRel for foreign
partitions because we can abort an insert into a foreign partition after
ExecFindPartition in ExecInsert, by checking to see if
resultRelInfo->ri_FdwRoutine is not NULL. Attached is a proposed patch
for that. Since COPY FROM has the same issue, I added regression tests
for COPY FROM as well as INSERT to file_fdw.

Best regards,
Etsuro Fujita

Attachment Content-Type Size
ExecSetupPartitionTupleRouting.patch text/plain 5.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2017-08-07 04:11:14 Re: Tuple-routing for certain partitioned tables not working as expected
Previous Message Craig Ringer 2017-08-07 03:38:19 Re: Effect of dropping a partitioned table's column over time