postgres.git / summary / log / commit / refs
commit f9282a65058b30f0e10fc5dff0171bbd6eb9a254
Author: David Rowley <drowley@postgresql.org>
Date: Fri Jul 31 03:37:12 2026 +0000
Fix issue with RANGE's DEFAULT partition pruning
Partition pruning for RANGE-partitioned tables could mistakenly prune
the DEFAULT partition in some cases when it was not valid to do so,
which could lead to rows missing from query results.
The only known cases where this could happen is when combining pruning
steps from an IS NOT NULL clause with other steps that matched to the
DEFAULT partition. This could occur due to RANGE partitioned tables
having two distinct internal representations for marking if the DEFAULT
partition should be scanned. The IS NOT NULL steps would mark the
"scan_default" boolean, but other steps created for different purposes
could mark a bound_offset Bitmapset, which would ultimately translate into
also scanning the default partition. This could all fail after multiple
steps were combined with a combine intersect operator, as that will
intersect the bound_offset bits and only set scan_default if all pruning
steps have that flag set. When both input steps to the intersect operator
had different representations of whether to scan the DEFAULT partition,
the resulting intersect step result would contain neither representation.
Here, we fix this by having the IS NOT NULL pruning result mark the
bound_offsets so that it uses both representations to mark that the
DEFAULT partition must be scanned.
Reported-by: Jacob Brazeal <jacob.brazeal@gmail.com>
Diagnosed-by: Jacob Brazeal <jacob.brazeal@gmail.com>
Author: David Rowley <dgrowleyml@gmail.com>
Discussion: https://postgr.es/m/CA+COZaDXrfTaBjLE=Z79MTaH6Xun1V4PeKxLvCNv8mXS8wn0rw@mail.gmail.com
Backpatch-through: 14
src/backend/partitioning/partprune.c | 10 ++----
src/test/regress/expected/partition_prune.out | 46 +++++++++++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 20 ++++++++++++
3 files changed, 68 insertions(+), 8 deletions(-)
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index e7c318bbcac..afe57ac297d 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -3031,15 +3031,9 @@ get_matching_range_bounds(PartitionPruneContext *context,
*/
if (nvalues == 0)
{
- /* ignore key space not covered by any partitions */
- if (partindices[minoff] < 0)
- minoff++;
- if (partindices[maxoff] < 0)
- maxoff--;
-
result->scan_default = partition_bound_has_default(boundinfo);
- Assert(partindices[minoff] >= 0 &&
- partindices[maxoff] >= 0);
+ Assert(partindices[minoff] >= -1 &&
+ partindices[maxoff] >= -1);
result->bound_offsets = bms_add_range(NULL, minoff, maxoff);
return result;
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 849049f9c51..0d21a2d027c 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -691,6 +691,52 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
(11 rows)
+-- Test cases for range partitioned tables with IN clauses.
+create table rangepart (a int) partition by range (a);
+create table rangepart1 partition of rangepart for values from (0) to (10);
+create table rangepart2 partition of rangepart for values from (10) to (20);
+create table rangepart_def partition of rangepart default;
+-- Ensure we scan all apart from the default partition
+explain (costs off) select * from rangepart where a in(5,15);
+ QUERY PLAN
+-------------------------------------------------
+ Append
+ -> Seq Scan on rangepart1 rangepart_1
+ Filter: (a = ANY ('{5,15}'::integer[]))
+ -> Seq Scan on rangepart2 rangepart_2
+ Filter: (a = ANY ('{5,15}'::integer[]))
+(5 rows)
+
+-- Ensure we scan only the default
+explain (costs off) select * from rangepart where a in(20,21);
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on rangepart_def rangepart
+ Filter: (a = ANY ('{20,21}'::integer[]))
+(2 rows)
+
+-- Ensure we scan only the default
+explain (costs off) select * from rangepart where a in(-1,20);
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on rangepart_def rangepart
+ Filter: (a = ANY ('{-1,20}'::integer[]))
+(2 rows)
+
+-- Ensure we scan all partitions
+explain (costs off) select * from rangepart where a is not null and a in(-1,5,15,20);
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Append
+ -> Seq Scan on rangepart1 rangepart_1
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{-1,5,15,20}'::integer[])))
+ -> Seq Scan on rangepart2 rangepart_2
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{-1,5,15,20}'::integer[])))
+ -> Seq Scan on rangepart_def rangepart_3
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{-1,5,15,20}'::integer[])))
+(7 rows)
+
+drop table rangepart;
-- multi-column keys
create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
create table mc3p_default partition of mc3p default;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 359a9208056..dac673ef80a 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -118,6 +118,26 @@ explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, i
explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
+-- Test cases for range partitioned tables with IN clauses.
+create table rangepart (a int) partition by range (a);
+create table rangepart1 partition of rangepart for values from (0) to (10);
+create table rangepart2 partition of rangepart for values from (10) to (20);
+create table rangepart_def partition of rangepart default;
+
+-- Ensure we scan all apart from the default partition
+explain (costs off) select * from rangepart where a in(5,15);
+
+-- Ensure we scan only the default
+explain (costs off) select * from rangepart where a in(20,21);
+
+-- Ensure we scan only the default
+explain (costs off) select * from rangepart where a in(-1,20);
+
+-- Ensure we scan all partitions
+explain (costs off) select * from rangepart where a is not null and a in(-1,5,15,20);
+
+drop table rangepart;
+
-- multi-column keys
create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
create table mc3p_default partition of mc3p default;
[parent: 4a791db9bdbd]