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.
Attached is a patch for fixing this issue.
Best regards,
Etsuro Fujita
[1]
https://www.postgresql.org/message-id/CAPmGK15%3Dc8Q-Ac3ogzZp_d6VsfRYSL2tD8zLwy_WYdrMXQhiCQ%40mail.gmail.com
fix-pruning-step-generation.patch
Description: Binary data
