Dear Gunce,
It is expected behavior since PostgreSQL 9.5. (It is not a bug, it is feature :)) CHECK constraints created locally on foreign tables are not actually enforced. These constraints are used by local planner if constraint exclusion is enabled. So, you can insert any row to foreign table which does not violate check constraints on the remote side. If you enable constraint exclusion then select the students_fdw foreign table for id > 10, no result returns to you. Please have a look to following example.
postgres=# \d+ students_fdw;
Foreign table "public.students_fdw"
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
id | integer | | | | | plain | |
name | character varying | | | | | extended | |
Partition of: students_local FOR VALUES FROM (1) TO (10)
Partition constraint: ((id IS NOT NULL) AND (id >= 1) AND (id < 10))
Server: remote_pg10
postgres=# select * from students_fdw;
id | name
-----+--------
5 | Samed
100 | Deneme
100 | Deneme
150 | Deneme
150 | Deneme
(5 rows)
postgres=# select * from students_fdw where id > 10;
id | name
----+------
(0 rows)
Foreign table "public.students_fdw"
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
id | integer | | | | | plain | |
name | character varying | | | | | extended | |
Partition of: students_local FOR VALUES FROM (1) TO (10)
Partition constraint: ((id IS NOT NULL) AND (id >= 1) AND (id < 10))
Server: remote_pg10
postgres=# select * from students_fdw;
id | name
-----+--------
5 | Samed
100 | Deneme
100 | Deneme
150 | Deneme
150 | Deneme
(5 rows)
postgres=# select * from students_fdw where id > 10;
id | name
----+------
(0 rows)
postgres=# set constraint_exclusion = off;
SET
postgres=# select * from students_fdw where id > 10;
id | name
-----+--------
100 | Deneme
100 | Deneme
150 | Deneme
150 | Deneme
(4 rows)
SET
postgres=# select * from students_fdw where id > 10;
id | name
-----+--------
100 | Deneme
100 | Deneme
150 | Deneme
150 | Deneme
(4 rows)
Best regards.
İyi çalışmalar.
Samed YILDIRIM
03.10.2018, 11:34, "Günce Kaya" <guncekaya14@xxxxxxxxx>:
--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.
To be more clear, I would like to add EXPLAIN ANALYZE script.artemis=# explain (analyze,verbose) insert into students values(2,'another name');
QUERY PLAN
--------------------------------------------------------------------------------------
------------------
Insert on public.students (cost=0.00..0.01 rows=1 width=36) (actual time=1.536..1.53
9 rows=0 loops=1)
Remote SQL: INSERT INTO public.students(id, name) VALUES ($1, $2)
-> Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.005..0.013 rows=1 loop
s=1)
Output: 2, 'another name'::character varying
Planning time: 0.063 ms
Execution time: 2.877 ms
(6 rows)We use push-down for INSERT statement but I think constraints should be checked before sending SQL statements then INSERT statements should be perform.If it's nature, why do we require to add unused constraint for FDW side only?Gunce Kaya
