Re: [HACKERS] Optimise default partition scanning while adding newpartition

On 2017/10/06 2:25, Robert Haas wrote:
> On Tue, Sep 26, 2017 at 4:27 AM, Amit Langote wrote:
>> I guess we don't need to squash, as they could be seen as implementing
>> different features. Reordering the patches helps though.  So, apply them
>> in this order:
>>
>> 1. My patch to teach ValidatePartitionConstraints() to skip scanning
>>    a partition's own partitions, which optimizes ATTACH PARTITION
>>    command's partition constraint validation scan (this also covers the
>>    case of scanning the default partition to validate its updated
>>    constraint when attaching a new partition)
>>
>> 2. Jeevan's patch to teach check_default_allows_bound() to skip scanning
>>    the default partition's own partitions, which covers the case of
>>    scanning the default partition to validate its updated constraint when
>>    adding a new partition using CREATE TABLE
>>
>> Attached 0001 and 0002 are ordered that way.
> 
> OK, I pushed all of this, spread out over 3 commits.  I reworked the
> test cases not to be entangled with the existing test cases, and also
> to test both of these highly-related features together.  Hopefully you
> like the result.

Thanks for committing.

I noticed that 6476b26115f3 doesn't use the same INFO message as
14f67a8ee28.  You can notice in the following example that the message
emitted (that the default partition scan is skipped) is different when a
new partition is added with CREATE TABLE and with ATTACH PARTITION,
respectively.

create table foo (a int, b char) partition by list (a);

-- default partition
create table food partition of foo default partition by list (b);

-- default partition's partition with the check constraint
create table fooda partition of food (check (not (a is not null and a = 1
and a = 2))) for values in ('a');

create table foo1 partition of foo for values in (1);
INFO:  partition constraint for table "fooda" is implied by existing
constraints
CREATE TABLE

create table foo2 (like foo);
alter table foo attach partition foo2 for values in (2);
INFO:  updated partition constraint for default partition "fooda" is
implied by existing constraints
ALTER TABLE

That's because it appears that you applied Jeevan's original patch.
Revised version of his patch that I had last posted contained the new
message. Actually the revised patch had not only addressed the case where
the default partition's child's scan is skipped, but also the case where
the scan of default partition itself is skipped.  As things stand now:

alter table food add constraint skip_check check (not (a is not null and
(a = any (array[1, 2]))));

create table foo1 partition of foo for values in (1);
INFO:  partition constraint for table "food" is implied by existing
constraints
CREATE TABLE

create table foo2 (like foo);
CREATE TABLE

alter table foo attach partition foo2 for values in (2);
INFO:  updated partition constraint for default partition "food" is
implied by existing constraints
ALTER TABLE


Attached a patch to modify the INFO messages in check_default_allows_bound.

Thanks,
Amit

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers