Re: Query related to alter table ... attach partition

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