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
