On 2018/01/23 14:35, Ashutosh Sharma wrote: > I have created a regular table with CHECK constraint on the partition > key column and it conflicts with the partition constraint but, still, > i could attach the table with the partitioned table. Here is what i am > trying to do, > > postgres[76308]=# create table part_tab (b int, a int) partition by range (a); > CREATE TABLE > > postgres[76308]=# create table part1 (a int, b int CHECK (a >= 5)); > CREATE TABLE > > postgres[76308]=# alter table part_tab attach partition part1 for > values from (0) to (5); -- the partition constraint applied here > conflicts with CHECK (a >= 5) applied on part1. > ALTER TABLE > > postgres[76308]=# \d+ part1; > Table "public.part1" > +--------+---------+-----------+----------+---------+---------+--------------+-------------+ > | Column | Type | Collation | Nullable | Default | Storage | Stats > target | Description | > +--------+---------+-----------+----------+---------+---------+--------------+-------------+ > | a | integer | | | | plain | > | | > | b | integer | | | | plain | > | | > +--------+---------+-----------+----------+---------+---------+--------------+-------------+ > Partition of: part_tab FOR VALUES FROM (0) TO (5) > Partition constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 5)) > Check constraints: > "part1_a_check" CHECK (a >= 5) > Options: storage_engine=zheap > > As shown in the description of part1 (child table) above, Partition > constraint i.e. (a >= 0) AND (a < 5) and the CHECK constraint a >= 5 > conflicts with each other but still alter table ... attach partition > succeeded. Isn't that a bug?
Hmm, I don't think it is. If you had inserted rows with a >= 5 into the table before attaching it as partition, error will be correctly reported about the rows that violate the partition constraint and attach will fail. create table part_tab (b int, a int) partition by range (a); create table part1 (a int, b int CHECK (a >= 5)); insert into part1 values (5); alter table part_tab attach partition part1 for values from (0) to (5); ERROR: partition constraint is violated by some row However, we don't make it fail because the table has a constraint that contradicts the partition constraint. Attach succeeds in the absence of any violating rows and the end result is that the table/partition has contradictory constraints (the existing constraint and the partition constraint) and that simply means no rows can be inserted into the table/partition. -- fail because of the existing constraint (insert through parent) insert into part_tab (a) values (4); ERROR: new row for relation "part1" violates check constraint "part1_a_check" -- fail because of the partition constraint (insert through parent) insert into part_tab (a) values (5); ERROR: no partition of relation "part_tab" found for row -- fail because of the existing constraint (insert directly) insert into part1 (a) values (4); ERROR: new row for relation "part1" violates check constraint "part1_a_check" -- fail because of the partition constraint (insert directly) insert into part1 (a) values (5); ERROR: new row for relation "part1" violates partition constraint But that's the user's mistake of failing to remove the existing constraint before attaching as partition for a different set of values. -- drop the existing constraint alter table part1 drop constraint part1_a_check; -- all fine insert into part_tab (a) values (4); -- (insert through parent) insert into part1 (a) values (4); -- (insert directly) Thanks, Amit
