Thread: Query related to alter table ... attach partition
Hi All, 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? -- With Regards, Ashutosh Sharma EnterpriseDB:http://www.enterprisedb.com
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
On Tue, Jan 23, 2018 at 11:49 AM, Amit Langote <Langote_Amit_f8@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
On 2018/01/23 15:55, Ashutosh Sharma wrote: >> 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? I did have such thoughts back when writing the patch, but we decided during the review that it wasn't worthwhile; see this email for example: https://www.postgresql.org/message-id/CA%2BTgmoaQABrsLQK4ms_4NiyavyJGS-b6ZFkZBBNC%2B-P5DjJNFA%40mail.gmail.com Or see this: create table foo (a int check (a > 1 and a < 1)); The above command doesn't fail even if the check expression in it is contradictory. If we think that the attach command in question should fail or issue WARNING, then the above command should too. Thanks, Amit
On Tue, Jan 23, 2018 at 12:25 PM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote: >> > > 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? > One can add constraints contradicting the partition constraints after the table is attached as partition. Moreover, one can add multiple constraints over the time that together contradict partition constraints. I don't think it's worth the effort to make sure that all constraints taken together contradict partition constraints or not. The downside is simply that the partition will remain empty forever, but then that's what user wants, since s/he has added the constraints. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company