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

On Tue, Jan 23, 2018 at 11:49 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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