Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table

On Mon, Jul 6, 2020 at 1:55 AM Dmitry Dolgov <9erthalion6@gmail.com> wrote:
> > On Sun, Jul 05, 2020 at 04:45:40PM +0900, Etsuro Fujita wrote:
> > I think a simple fix for this issue would be to just give up on
> > generating pruning steps if prefix contains no clauses, like the
> > attached.
>
> Yes, I agree. Not generating any pruning steps if prefix has no clauses
> was my first idea, but looking at attached patch I've apparently missed
> one part in the implementation and was under the false impression it
> wouldn't work.

I think the fix I proposed handles both cases reported in BUG #16500
and BUG #16501, but I noticed there is a case that the fix doesn’t
cover.  Here is an example:

postgres=# create table rp_prefix_test2 (a int, b int, c int)
partition by range (a, b, c);
postgres=# create table rp_prefix_test2_p1 partition of
rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10);
postgres=# create table rp_prefix_test2_p2 partition of
rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10);
postgres=# select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0;

Even with the fix, this fails an assertion defined in
get_steps_using_prefix().  The reason for that is: that function
assumes that the passed-in prefix contains at least one clause for
each earlier partition key, but the caller (i.e.,
gen_prune_steps_from_opexps()) doesn’t take it into account.  In the
example, get_steps_using_prefix() is called with c >= 0 plus b = 1 as
a prefix, which doesn’t contain a clause for the first partition key
"a", breaking that assumption, which leads to the error.  To fix, I
modified the previous patch further so that get_steps_using_prefix()
is called only when prefix created contains clauses for each earlier
partition key.  While at it, I simplified the preprocessing to sort
out btree clauses, because the preprocessing would not be useful
anymore, and because keeping the preprocessing as-is would only reduce
the chance of getting more partition pruning steps.  Attached is an
updated version of the patch.  Maybe I'm missing something, but I
suspect that hash partitioning would also have a similar issue, but I
didn’t look at it closely.  Will do.

Thanks for reviewing!

Best regards,
Etsuro Fujita