Yet another issue with step generation in partition pruning
Commit 13838740f fixed some issues with step generation in partition
pruning, but as I mentioned in [1]/messages/by-id/CAPmGK15=c8Q-Ac3ogzZp_d6VsfRYSL2tD8zLwy_WYdrMXQhiCQ@mail.gmail.com, I noticed that there is yet
another issue: get_steps_using_prefix() assumes that clauses in the
passed-in prefix list are sorted in ascending order of their partition
key numbers, but the caller (i.e., gen_prune_steps_from_opexps())
doesn’t ensure that in the case of range partitioning, leading to an
assertion failure. Here is an example causing such a failure, which
would happen with/without that commit:
create table rp_prefix_test2 (a int, b int, c int) partition by range (a, b, c);
create table rp_prefix_test2_p1 partition of rp_prefix_test2 for
values from (1, 1, 0) to (1, 1, 10);
create table rp_prefix_test2_p2 partition of rp_prefix_test2 for
values from (2, 2, 0) to (2, 2, 10);
select * from rp_prefix_test2 where a <= 1 and b <= 1 and b = 1 and c <= 0;
I don't think we write queries like this, but for this query, the
caller would create the prefix list for the last partition key “c”
{b=1, a<=1, b<=1} (the clauses are not sorted properly!), then calling
get_steps_using_prefix(), which leads to an assertion failure.
Attached is a patch for fixing this issue.
Best regards,
Etsuro Fujita
[1]: /messages/by-id/CAPmGK15=c8Q-Ac3ogzZp_d6VsfRYSL2tD8zLwy_WYdrMXQhiCQ@mail.gmail.com
Attachments:
fix-pruning-step-generation.patchapplication/octet-stream; name=fix-pruning-step-generation.patchDownload+90-57
Fujita-san,
Thanks a lot for your time on fixing these multi-column range
partition pruning issues. I'm sorry that I failed to notice the
previous two reports on -bugs for which you committed a fix last week.
On Tue, Aug 4, 2020 at 9:46 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Commit 13838740f fixed some issues with step generation in partition
pruning, but as I mentioned in [1], I noticed that there is yet
another issue: get_steps_using_prefix() assumes that clauses in the
passed-in prefix list are sorted in ascending order of their partition
key numbers, but the caller (i.e., gen_prune_steps_from_opexps())
doesn’t ensure that in the case of range partitioning, leading to an
assertion failure. Here is an example causing such a failure, which
would happen with/without that commit:create table rp_prefix_test2 (a int, b int, c int) partition by range (a, b, c);
create table rp_prefix_test2_p1 partition of rp_prefix_test2 for
values from (1, 1, 0) to (1, 1, 10);
create table rp_prefix_test2_p2 partition of rp_prefix_test2 for
values from (2, 2, 0) to (2, 2, 10);
select * from rp_prefix_test2 where a <= 1 and b <= 1 and b = 1 and c <= 0;I don't think we write queries like this, but for this query, the
caller would create the prefix list for the last partition key “c”
{b=1, a<=1, b<=1} (the clauses are not sorted properly!), then calling
get_steps_using_prefix(), which leads to an assertion failure.
That analysis is spot on.
Attached is a patch for fixing this issue.
I have looked at the patch and played around with it using the
regression tests you've added recently. I was not able to find any
results that looked surprising.
Thanks again.
--
Amit Langote
EnterpriseDB: http://www.enterprisedb.com
Amit-san,
On Wed, Aug 5, 2020 at 5:13 PM Amit Langote <amitlangote09@gmail.com> wrote:
Thanks a lot for your time on fixing these multi-column range
partition pruning issues. I'm sorry that I failed to notice the
previous two reports on -bugs for which you committed a fix last week.
No problem.
On Tue, Aug 4, 2020 at 9:46 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Attached is a patch for fixing this issue.
I have looked at the patch and played around with it using the
regression tests you've added recently. I was not able to find any
results that looked surprising.
That's good to hear! Thanks for reviewing! Will push the patch tomorrow.
Best regards,
Etsuro Fujita
On Thu, Aug 6, 2020 at 12:20 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Will push the patch tomorrow.
Done. (I didn't have time for this, because I was terribly busy with
other stuff.)
Best regards,
Etsuro Fujita
On Fri, Aug 7, 2020 at 2:55 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Thu, Aug 6, 2020 at 12:20 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Will push the patch tomorrow.
Done. (I didn't have time for this, because I was terribly busy with
other stuff.)
I mean I didn't have time for this *yesterday*.
Best regards,
Etsuro Fujita