Обсуждение: 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