Partitioning with foreign tables.

Hello,

I have a question about partitioning with FDW and I have some confusion
about using constraints. I would like to share with you.

I generated a partitioned table(students_fdw) has two partition
tables(students_p1,students_p2) on Postgres 10 in remote side. You can see
which constraint partitioned table has.

Remote side:

[pg10] # \d+ students_fdw

Table "public.students_fdw"

Column | Type | Collation | Nullable | Default | Storage |
Stats

target | Description

--------+-------------------+-----------+----------+---------+----------+-------

-------+-------------

id | integer | | not null | | plain |

|

name | character varying | | not null | | extended |

|

Partition key: RANGE (id)

Partitions: students_p1 FOR VALUES FROM (1) TO (100),

students_p2 FOR VALUES FROM (100) TO (200)

Internal server database side;

I generated a foreign table(students_fdw).

I created a new partitioned table which is called students_local.
Students_fdw foreign table is going to be a partition table of
students_local table.

artemis=# create table students_local (id integer, name character varying)
partition by range(id);

CREATE TABLE

After generating students_local table as partitioned table, I ATTACH
foreign table(students_fdw)which is also a partitioned table in remote.
Students_fdw table become a partition table of students_local table. The
constraint in here is id should be between 1 to 10 because of following
script.

artemis=# alter table students_local attach partition students_fdw for
values from (1) to (10);

ALTER TABLE

After creating partitioned table and attaching a foreign table as a
partition table to it, I checked If it works well.

artemis=# select * from students_local limit 1;

id | name

-----+----------------------------------

100 | 28fb8f3a34d5c125d31de5e44735ec40

(1 row)

Then I inserted a row like id=1. It worked.

artemis=# insert into students_fdw values(1,'a name');

INSERT 0 1

Then I tried to insert another row for id=11. Insert statement also worked.
But I think the insert statement should not work because I’ve gave a
constraint as id between 1 to 10 for partitioned table that is in local.

artemis=# insert into students_fdw values(11,'another name');

INSERT 0 1

I checked row counts if there is a row number greater than 1.

artemis=# select id from students_fdw group by id having count(*)>1;

id

----

11

1

(2 rows)

After all, I tried to insert another row like id=200 (the foreign table has
a constraint in remote side like id can not be inserted if id>=200 because
this foreign table is a partitioned table in remote side and we should care
about constraints on it.)

I tried to insert a row for id=200(this is out of constraint for
partitioned table in foreign server) in local. Of course, I couldn’t do it.

artemis=# insert into students_fdw values(200,'another name');

ERROR: no partition of relation "students_fdw" found for row

DETAIL: Partition key of the failing row contains (id) = (200).

CONTEXT: Remote SQL command: INSERT INTO public.students_fdw(id, name)
VALUES ($1, $2)

Then I’ve got an error. I could not insert this row because of partitioned
table constraint which is for foreign table.

I couldn’t insert a row where id=200. But I think I should not insert a row
where id=11 because of constraints of partitioned table in local.

if constraint doesn’t match even if foreign table’s constraint is matched,
I should not insert row but I could insert a row out of constraints on
partitioned table(studensts_local) which is in internal server database.

If I insert a row to partitioned table which is in local, a partitioned
table’s constraints should work. If I add a constraint on a partitioned
table, I should be make sure I can not insert a row which is out of
partitioned table’s constraints. But I could. I think, partitioned table’s
constraints should work with regard to environment. If I work on local,
constraints work for local tables and should not consider other
environment’s constraints.

If constraints don’t work in locally, why am I add a constraint to a
partitioned table when foreign table which is going to be part of
partitioned table?

Probably, there is some proper explanation for this case but the situation
made me confused. And I would like to heard the real reason for this.

Regards,

Günce

Linkedin <https://tr.linkedin.com/in/guncekaya> - Twitter
<https://twitter.com/gguncesi> - Blog
<http://www.guncekaya.blogspot.com.tr/>