On Tue, Jan 23, 2018 at 11:49 AM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > 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. >
Well, that means the attach would only fail when a table contains some value that doesn't fall in a partition range. > 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. > That's right. But, shouldn't a partition that not at all fall in the partition range be rejected when user tries to attach it. I feel we should at least try throwing a WARNING message for it. Thoughts? > -- 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 > -- With Regards, Ashutosh Sharma EnterpriseDB:http://www.enterprisedb.com
