pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix incorrect multi-column RANGE partition pruning 7+ messages / 1 participants [nested] [flat]
* pgsql: Fix incorrect multi-column RANGE partition pruning @ 2026-08-28 01:10 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: David Rowley @ 2026-08-28 01:10 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix incorrect multi-column RANGE partition pruning When performing partition pruning with a RANGE partitioned table where the pruning quals are only present for a leading prefix of the partition key, it was possible that partition pruning would accidentally prune away some partitions which shouldn't be pruned and include some partitions that were not needed. This happened due to an incorrectly coded loop bound which was terminating the loop when the bound reached the first or last element in the partition bound array. This resulted in those end elements not being checked in cases where they should be checked. It appears that it might have been coded this way to avoid stepping off the array, but that was done incorrectly as it failed to take into account the direction of travel through the array (the loop can go forwards or backwards). I.e., it's valid to loop when 'off' is the last element if we're going backwards through the array, and valid to loop if 'off' is 0 and we're looping forward through the array, but the code as it was didn't allow that. Here we fix this by moving the loop condition check to after we've calculated the array element to process, and break from the loop if that element is beyond either end of the array. Example of accidentally pruned partition: p: partition by range (a, b); p1: for values from (1, 4) to (1, 7); p2: for values from (1, 7) to (3, 8); p3: for values from (4, 8) to (6, 9); def: default; select * from p where a <= 1; Here p2 was pruned by mistake. Example of accidentally not pruning a partition: p: partition by range (a, b); p1: for values from (7, 2) to (7, 7); def: default; select * from p where a > 7; No partitions would be pruned in this case, despite it being impossible for matching rows to exist in p1. Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com Backpatch-through: 14 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/6e5d5680b555523a3d5c547aa22df0267860344a Modified Files -------------- src/backend/partitioning/partprune.c | 14 +++++++++--- src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++ 3 files changed, 64 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix incorrect multi-column RANGE partition pruning @ 2026-08-28 01:11 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: David Rowley @ 2026-08-28 01:11 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix incorrect multi-column RANGE partition pruning When performing partition pruning with a RANGE partitioned table where the pruning quals are only present for a leading prefix of the partition key, it was possible that partition pruning would accidentally prune away some partitions which shouldn't be pruned and include some partitions that were not needed. This happened due to an incorrectly coded loop bound which was terminating the loop when the bound reached the first or last element in the partition bound array. This resulted in those end elements not being checked in cases where they should be checked. It appears that it might have been coded this way to avoid stepping off the array, but that was done incorrectly as it failed to take into account the direction of travel through the array (the loop can go forwards or backwards). I.e., it's valid to loop when 'off' is the last element if we're going backwards through the array, and valid to loop if 'off' is 0 and we're looping forward through the array, but the code as it was didn't allow that. Here we fix this by moving the loop condition check to after we've calculated the array element to process, and break from the loop if that element is beyond either end of the array. Example of accidentally pruned partition: p: partition by range (a, b); p1: for values from (1, 4) to (1, 7); p2: for values from (1, 7) to (3, 8); p3: for values from (4, 8) to (6, 9); def: default; select * from p where a <= 1; Here p2 was pruned by mistake. Example of accidentally not pruning a partition: p: partition by range (a, b); p1: for values from (7, 2) to (7, 7); def: default; select * from p where a > 7; No partitions would be pruned in this case, despite it being impossible for matching rows to exist in p1. Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com Backpatch-through: 14 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/7a74e5ed92d4e36f5b8da3db66457d6f32cf80f3 Modified Files -------------- src/backend/partitioning/partprune.c | 14 +++++++++--- src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++ 3 files changed, 64 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix incorrect multi-column RANGE partition pruning @ 2026-08-28 01:11 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: David Rowley @ 2026-08-28 01:11 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix incorrect multi-column RANGE partition pruning When performing partition pruning with a RANGE partitioned table where the pruning quals are only present for a leading prefix of the partition key, it was possible that partition pruning would accidentally prune away some partitions which shouldn't be pruned and include some partitions that were not needed. This happened due to an incorrectly coded loop bound which was terminating the loop when the bound reached the first or last element in the partition bound array. This resulted in those end elements not being checked in cases where they should be checked. It appears that it might have been coded this way to avoid stepping off the array, but that was done incorrectly as it failed to take into account the direction of travel through the array (the loop can go forwards or backwards). I.e., it's valid to loop when 'off' is the last element if we're going backwards through the array, and valid to loop if 'off' is 0 and we're looping forward through the array, but the code as it was didn't allow that. Here we fix this by moving the loop condition check to after we've calculated the array element to process, and break from the loop if that element is beyond either end of the array. Example of accidentally pruned partition: p: partition by range (a, b); p1: for values from (1, 4) to (1, 7); p2: for values from (1, 7) to (3, 8); p3: for values from (4, 8) to (6, 9); def: default; select * from p where a <= 1; Here p2 was pruned by mistake. Example of accidentally not pruning a partition: p: partition by range (a, b); p1: for values from (7, 2) to (7, 7); def: default; select * from p where a > 7; No partitions would be pruned in this case, despite it being impossible for matching rows to exist in p1. Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com Backpatch-through: 14 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/7c503d683ca06d14052614ff39ec8648d1d722d0 Modified Files -------------- src/backend/partitioning/partprune.c | 14 +++++++++--- src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++ 3 files changed, 64 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix incorrect multi-column RANGE partition pruning @ 2026-08-28 01:12 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: David Rowley @ 2026-08-28 01:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix incorrect multi-column RANGE partition pruning When performing partition pruning with a RANGE partitioned table where the pruning quals are only present for a leading prefix of the partition key, it was possible that partition pruning would accidentally prune away some partitions which shouldn't be pruned and include some partitions that were not needed. This happened due to an incorrectly coded loop bound which was terminating the loop when the bound reached the first or last element in the partition bound array. This resulted in those end elements not being checked in cases where they should be checked. It appears that it might have been coded this way to avoid stepping off the array, but that was done incorrectly as it failed to take into account the direction of travel through the array (the loop can go forwards or backwards). I.e., it's valid to loop when 'off' is the last element if we're going backwards through the array, and valid to loop if 'off' is 0 and we're looping forward through the array, but the code as it was didn't allow that. Here we fix this by moving the loop condition check to after we've calculated the array element to process, and break from the loop if that element is beyond either end of the array. Example of accidentally pruned partition: p: partition by range (a, b); p1: for values from (1, 4) to (1, 7); p2: for values from (1, 7) to (3, 8); p3: for values from (4, 8) to (6, 9); def: default; select * from p where a <= 1; Here p2 was pruned by mistake. Example of accidentally not pruning a partition: p: partition by range (a, b); p1: for values from (7, 2) to (7, 7); def: default; select * from p where a > 7; No partitions would be pruned in this case, despite it being impossible for matching rows to exist in p1. Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com Backpatch-through: 14 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2d8494e24f75982a5663ea5ca55195ebf392fe76 Modified Files -------------- src/backend/partitioning/partprune.c | 14 +++++++++--- src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++ 3 files changed, 64 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix incorrect multi-column RANGE partition pruning @ 2026-08-28 01:12 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: David Rowley @ 2026-08-28 01:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix incorrect multi-column RANGE partition pruning When performing partition pruning with a RANGE partitioned table where the pruning quals are only present for a leading prefix of the partition key, it was possible that partition pruning would accidentally prune away some partitions which shouldn't be pruned and include some partitions that were not needed. This happened due to an incorrectly coded loop bound which was terminating the loop when the bound reached the first or last element in the partition bound array. This resulted in those end elements not being checked in cases where they should be checked. It appears that it might have been coded this way to avoid stepping off the array, but that was done incorrectly as it failed to take into account the direction of travel through the array (the loop can go forwards or backwards). I.e., it's valid to loop when 'off' is the last element if we're going backwards through the array, and valid to loop if 'off' is 0 and we're looping forward through the array, but the code as it was didn't allow that. Here we fix this by moving the loop condition check to after we've calculated the array element to process, and break from the loop if that element is beyond either end of the array. Example of accidentally pruned partition: p: partition by range (a, b); p1: for values from (1, 4) to (1, 7); p2: for values from (1, 7) to (3, 8); p3: for values from (4, 8) to (6, 9); def: default; select * from p where a <= 1; Here p2 was pruned by mistake. Example of accidentally not pruning a partition: p: partition by range (a, b); p1: for values from (7, 2) to (7, 7); def: default; select * from p where a > 7; No partitions would be pruned in this case, despite it being impossible for matching rows to exist in p1. Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com Backpatch-through: 14 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a0396c8e33d81f8644390f2905079cd4001d5e71 Modified Files -------------- src/backend/partitioning/partprune.c | 14 +++++++++--- src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++ 3 files changed, 64 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix incorrect multi-column RANGE partition pruning @ 2026-08-28 01:12 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: David Rowley @ 2026-08-28 01:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix incorrect multi-column RANGE partition pruning When performing partition pruning with a RANGE partitioned table where the pruning quals are only present for a leading prefix of the partition key, it was possible that partition pruning would accidentally prune away some partitions which shouldn't be pruned and include some partitions that were not needed. This happened due to an incorrectly coded loop bound which was terminating the loop when the bound reached the first or last element in the partition bound array. This resulted in those end elements not being checked in cases where they should be checked. It appears that it might have been coded this way to avoid stepping off the array, but that was done incorrectly as it failed to take into account the direction of travel through the array (the loop can go forwards or backwards). I.e., it's valid to loop when 'off' is the last element if we're going backwards through the array, and valid to loop if 'off' is 0 and we're looping forward through the array, but the code as it was didn't allow that. Here we fix this by moving the loop condition check to after we've calculated the array element to process, and break from the loop if that element is beyond either end of the array. Example of accidentally pruned partition: p: partition by range (a, b); p1: for values from (1, 4) to (1, 7); p2: for values from (1, 7) to (3, 8); p3: for values from (4, 8) to (6, 9); def: default; select * from p where a <= 1; Here p2 was pruned by mistake. Example of accidentally not pruning a partition: p: partition by range (a, b); p1: for values from (7, 2) to (7, 7); def: default; select * from p where a > 7; No partitions would be pruned in this case, despite it being impossible for matching rows to exist in p1. Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com Backpatch-through: 14 Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/1a501b6e2812e43aaa320e535f7ff1f0c629ae76 Modified Files -------------- src/backend/partitioning/partprune.c | 14 +++++++++--- src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++ 3 files changed, 64 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix incorrect multi-column RANGE partition pruning @ 2026-08-28 01:13 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: David Rowley @ 2026-08-28 01:13 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix incorrect multi-column RANGE partition pruning When performing partition pruning with a RANGE partitioned table where the pruning quals are only present for a leading prefix of the partition key, it was possible that partition pruning would accidentally prune away some partitions which shouldn't be pruned and include some partitions that were not needed. This happened due to an incorrectly coded loop bound which was terminating the loop when the bound reached the first or last element in the partition bound array. This resulted in those end elements not being checked in cases where they should be checked. It appears that it might have been coded this way to avoid stepping off the array, but that was done incorrectly as it failed to take into account the direction of travel through the array (the loop can go forwards or backwards). I.e., it's valid to loop when 'off' is the last element if we're going backwards through the array, and valid to loop if 'off' is 0 and we're looping forward through the array, but the code as it was didn't allow that. Here we fix this by moving the loop condition check to after we've calculated the array element to process, and break from the loop if that element is beyond either end of the array. Example of accidentally pruned partition: p: partition by range (a, b); p1: for values from (1, 4) to (1, 7); p2: for values from (1, 7) to (3, 8); p3: for values from (4, 8) to (6, 9); def: default; select * from p where a <= 1; Here p2 was pruned by mistake. Example of accidentally not pruning a partition: p: partition by range (a, b); p1: for values from (7, 2) to (7, 7); def: default; select * from p where a > 7; No partitions would be pruned in this case, despite it being impossible for matching rows to exist in p1. Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com Backpatch-through: 14 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/978caef1936fc06a6dcc75283115ede953ab6d34 Modified Files -------------- src/backend/partitioning/partprune.c | 14 +++++++++--- src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++ 3 files changed, 64 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-08-28 01:13 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-08-28 01:10 pgsql: Fix incorrect multi-column RANGE partition pruning David Rowley <drowley@postgresql.org> 2026-08-28 01:11 pgsql: Fix incorrect multi-column RANGE partition pruning David Rowley <drowley@postgresql.org> 2026-08-28 01:11 pgsql: Fix incorrect multi-column RANGE partition pruning David Rowley <drowley@postgresql.org> 2026-08-28 01:12 pgsql: Fix incorrect multi-column RANGE partition pruning David Rowley <drowley@postgresql.org> 2026-08-28 01:12 pgsql: Fix incorrect multi-column RANGE partition pruning David Rowley <drowley@postgresql.org> 2026-08-28 01:12 pgsql: Fix incorrect multi-column RANGE partition pruning David Rowley <drowley@postgresql.org> 2026-08-28 01:13 pgsql: Fix incorrect multi-column RANGE partition pruning David Rowley <drowley@postgresql.org>
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