Thread: Foreign tables don't enforce the partition constraint

We don't enforce the constraints defined on foreign tables in ExecInsert()
and ExecUpdate().  (COPY FROM does not support foreign tables at all.)
Since partition constraints are enforced using ExecConstraints() which is
not called for foreign tables, they will not be checked if one inserts
directly into foreign partitions.  So:

create table p (a int) partition by list (a);
create table p1t (like p);
create table p2t (like p);
create foreign table p1 partition of p for values in (1) server loopback options (table_name 'p1t');
create foreign table p2 partition of p for values in (2) server loopback options (table_name 'p2t');
insert into p1 values (2);  -- ungood
insert into p2 values (1);  -- ungood

While we have the ability to mark check constraints as being NOT VALID so
that planner can ignore them, partition constraints are assumed to
*always* hold, giving possibly surprising results.

explain (costs off) select * from p where a = 1;       QUERY PLAN
--------------------------Append  ->  Foreign Scan on p1
(2 rows)

select * from p where a = 1;a
---
(0 rows)

explain (costs off) select * from p where a = 2;       QUERY PLAN
--------------------------Append  ->  Foreign Scan on p2
(2 rows)

select * from p where a = 2;a
---
(0 rows)

Should we do something about this (treat as an open item)?

Thanks,
Amit

On Fri, Mar 31, 2017 at 1:36 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> We don't enforce the constraints defined on foreign tables in ExecInsert()
> and ExecUpdate().  (COPY FROM does not support foreign tables at all.)
> Since partition constraints are enforced using ExecConstraints() which is
> not called for foreign tables, they will not be checked if one inserts
> directly into foreign partitions.  So:
>
> create table p (a int) partition by list (a);
> create table p1t (like p);
> create table p2t (like p);
> create foreign table p1 partition of p for values in (1)
>   server loopback options (table_name 'p1t');
> create foreign table p2 partition of p for values in (2)
>   server loopback options (table_name 'p2t');
> insert into p1 values (2);  -- ungood
> insert into p2 values (1);  -- ungood
>
> While we have the ability to mark check constraints as being NOT VALID so
> that planner can ignore them, partition constraints are assumed to
> *always* hold, giving possibly surprising results.
>
> explain (costs off) select * from p where a = 1;
>         QUERY PLAN
> --------------------------
>  Append
>    ->  Foreign Scan on p1
> (2 rows)
>
> select * from p where a = 1;
>  a
> ---
> (0 rows)
>
> explain (costs off) select * from p where a = 2;
>         QUERY PLAN
> --------------------------
>  Append
>    ->  Foreign Scan on p2
> (2 rows)
>
> select * from p where a = 2;
>  a
> ---
> (0 rows)
>
> Should we do something about this (treat as an open item)?

Per https://www.postgresql.org/docs/devel/static/sql-createforeigntable.html,
constraints on the foreign table should represent a constraint that is
being enforced by the remote server. Similarly, a partition constraint
should also be enforced at the foreign server. Probably we should
update documentation of create foreign table to mention this. We have
updated ALTER TABLE ATTACH PARTITION documentation with a note on
foreign tables.
-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company