Re: Problem, partition pruning for prepared statement with IS NULL clause.

On Mon, 9 Oct 2023 at 12:26, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Sat, 7 Oct 2023 at 03:11, Sergei Glukhov <s(dot)glukhov(at)postgrespro(dot)ru> wrote:
> > I noticed that combination of prepared statement with generic plan and
> > 'IS NULL' clause could lead partition pruning to crash.
>
> > Test case:
> > ------
> > set plan_cache_mode to force_generic_plan;
> > prepare stmt AS select * from hp where a is null and b = $1;
> > explain execute stmt('xxx');
>
> Thanks for the detailed report and proposed patch.
>
> I think your proposed fix isn't quite correct. I think the problem
> lies in InitPartitionPruneContext() where we assume that the list
> positions of step->exprs are in sync with the keyno. If you look at
> perform_pruning_base_step() the code there makes a special effort to
> skip over any keyno when a bit is set in opstep->nullkeys.

I've now also pushed the fix for the incorrect logic for nullkeys in
ExecInitPruningContext().

I didn't quite find a test to make this work for v11. I tried calling
execute 5 times as we used to have to before the plan_cache_mode GUC
was added in v12, but the test case kept picking the custom plan. So I
ended up pushing v11 without any test. This goes out of support in ~1
month, so I'm not too concerned about the lack of test. I did do a
manual test to ensure it works with:

create table hp (a int, b text, c int) partition by hash (a, b);
create table hp0 partition of hp for values with (modulus 4, remainder 0);
create table hp3 partition of hp for values with (modulus 4, remainder 3);
create table hp1 partition of hp for values with (modulus 4, remainder 1);
create table hp2 partition of hp for values with (modulus 4, remainder 2);

prepare hp_q1 (text) as select * from hp where a is null and b = $1;

(set breakpoint in choose_custom_plan() and have it return false when
we hit it.)

explain (costs off) execute hp_q1('xxx');

David