Обсуждение: 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