Problem, partition pruning for prepared statement with IS NULL clause.
Sergei Glukhov <s.glukhov@postgrespro.ru>
From: Sergei Glukhov <s.glukhov@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Date: 2023-10-06T14:09:45Z
Lists: pgsql-hackers
Attachments
- fix-partition-pruning-with-isnull.patch (text/x-patch) patch
Hello postgres hackers,
I noticed that combination of prepared statement with generic plan and
'IS NULL' clause could lead partition pruning to crash.
Affected versions start from 12 it seems.
'How to repeat' below and an attempt to fix it is in attachment.
Data set:
------
create function part_hashint4_noop(value int4, seed int8)
returns int8 as $$
select value + seed;
$$ language sql strict immutable parallel safe;
create operator class part_test_int4_ops for type int4 using hash as
operator 1 =,
function 2 part_hashint4_noop(int4, int8);
create function part_hashtext_length(value text, seed int8)
returns int8 as $$
select length(coalesce(value, ''))::int8
$$ language sql strict immutable parallel safe;
create operator class part_test_text_ops for type text using hash as
operator 1 =,
function 2 part_hashtext_length(text, int8);
create table hp (a int, b text, c int)
partition by hash (a part_test_int4_ops, b part_test_text_ops);
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);
insert into hp values (null, null, 0);
insert into hp values (1, null, 1);
insert into hp values (1, 'xxx', 2);
insert into hp values (null, 'xxx', 3);
insert into hp values (2, 'xxx', 4);
insert into hp values (1, 'abcde', 5);
------
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');
------
Regargs,
Gluh
Commits
-
Fix runtime partition pruning for HASH partitioned tables
- 2759924f33a6 11.22 landed
- 3cc0c25172b2 12.17 landed
- 890a73ba3ac0 13.13 landed
- d26f33c32436 14.10 landed
- 1e81d3e6e0a0 15.5 landed
- 595db9e9cc41 16.1 landed
- d9e46dfb78ab 17.0 landed
-
Fix incorrect step generation in HASH partition pruning
- 07f261b3172d 11.22 landed
- cd259de50ac5 12.17 landed
- 45c4b2a3aa72 13.13 landed
- cd15bff4814b 14.10 landed
- 916adc7c50bb 15.5 landed
- 6352f1627641 16.1 landed
- f0c409d9c7a6 17.0 landed