From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Query related to alter table ... attach partition |
Date: | 2018-01-23 06:19:53 |
Message-ID: | ecaec006-7ce0-76e1-ade4-7030ec5629b7@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2018-01-23 06:44:47 | Re: [Sender Address Forgery]Re: [Sender Address Forgery]Re: [Sender Address Forgery]Re: [HACKERS] path toward faster partition pruning |
Previous Message | Masahiko Sawada | 2018-01-23 05:35:42 | Failed to request an autovacuum work-item in silence |