Re: Optimise default partition scanning while adding new partition

On Tue, Sep 26, 2017 at 4:27 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2017/09/16 1:57, Amit Langote wrote:
>> On Sat, Sep 16, 2017 at 12:59 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> I believe the intended advantage of the current system is that if you
>>> specify multiple operations in a single ALTER TABLE command, you only
>>> do one scan rather than having a second scan per operation. If that's
>>> currently working, we probably don't want to make it stop working.
>>
>> OK.
>>
>> How about squash Jeevan's and my patch, so both
>> check_default_allows_bound() and ValidatePartitionConstraints() know
>> to scan default partition's children and there won't be any surprises
>> in the regression test output as you found after applying just the
>> Jeevan's patch. Unfortunately, I'm not able to post such a patch
>> right now.
>
> 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 the patches and the analysis.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company