From: | Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> |
---|---|
To: | Dmitry Dolgov <9erthalion6(at)gmail(dot)com> |
Cc: | hisanori(dot)kobayashi(dot)bp(at)nttdata(dot)com, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table |
Date: | 2020-07-08 06:15:57 |
Message-ID: | CAPmGK15P6Jc8MPnRdfQe=P+i-jKAXrkE9T3nNo1BQvQpMFvrkw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Mon, Jul 6, 2020 at 1:55 AM Dmitry Dolgov <9erthalion6(at)gmail(dot)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
Attachment | Content-Type | Size |
---|---|---|
prefix-efujita-v2.patch | application/octet-stream | 9.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Fahar Abbas | 2020-07-08 06:59:50 | Re: Installation error |
Previous Message | Pavel Stehule | 2020-07-08 04:13:45 | Re: Bug: Very poor query optimization by Postgres |