From: Sergei Glukhov <s.glukhov@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Subject: Problem, partition pruning for prepared statement with IS NULL clause.
Date: Fri, 6 Oct 2023 18:09:45 +0400
Message-ID: <d05b26fa-af54-27e1-f693-6c31590802fa@postgrespro.ru> (raw)
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
Attachments:
[text/x-patch] fix-partition-pruning-with-isnull.patch (2.7K, ../d05b26fa-af54-27e1-f693-6c31590802fa@postgrespro.ru/2-fix-partition-pruning-with-isnull.patch)
download | inline diff:diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex 7179b22a05..0fbc2dba33 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -3383,10 +3383,21 @@ perform_pruning_base_step(PartitionPruneContext *context,
bool isnull;
Oid cmpfn;
+ /*+ * IS NULL clause is not added into array of ExprStates+ * and thus 'stateidx' based on 'keyno' cannot be used to get+ * ExprStates array element. Use the index based on 'nvalues'+ * in order to obtain proper datum from the array.+ */+ int exprstateidx =+ PruneCxtStateIdx(context->partnatts, opstep->step.step_id,+ nvalues);+
expr = lfirst(lc1);
stateidx = PruneCxtStateIdx(context->partnatts,
opstep->step.step_id, keyno);
- partkey_datum_from_expr(context, expr, stateidx,++ partkey_datum_from_expr(context, expr, exprstateidx,
&datum, &isnull);
/*
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex bb1223e2b1..a16e08a405 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -1921,6 +1921,18 @@ explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and
Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
(7 rows)
+-- test partition pruning with prepared statement and IS NULL clause.+prepare stmt AS select * from hp where a is null and b = $1;+explain (costs off) execute stmt('xxx');+ QUERY PLAN +--------------------------------------------+ Append+ Subplans Removed: 3+ -> Seq Scan on hp2 hp_1+ Filter: ((a IS NULL) AND (b = $1))+(4 rows)++deallocate stmt;
-- test pruning when not all the partitions exist
drop table hp1;
drop table hp3;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sqlindex 83fed54b8c..9cafbdd538 100644--- a/src/test/regress/sql/partition_prune.sql+++ b/src/test/regress/sql/partition_prune.sql@@ -374,6 +374,11 @@ explain (costs off) select * from hp where a = 2 and b = 'xxx';
explain (costs off) select * from hp where a = 1 and b = 'abcde';
explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and b = 'xxx') or (a is null and b is null);
+-- test partition pruning with prepared statement and IS NULL clause.+prepare stmt AS select * from hp where a is null and b = $1;+explain (costs off) execute stmt('xxx');+deallocate stmt;+
-- test pruning when not all the partitions exist
drop table hp1;
drop table hp3;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: s.glukhov@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Problem, partition pruning for prepared statement with IS NULL clause.
In-Reply-To: <d05b26fa-af54-27e1-f693-6c31590802fa@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox