| From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | More partition pruning bugs with multi-column RANGE partitions |
| Date: | 2026-08-25 11:02:16 |
| Message-ID: | CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
There is still a pending bug report in [1] about the DEFAULT partition
still being pruned incorrectly in some cases. This one isn't the same
issue, but I did find this one as a result of looking into Ewan's
report (which I'm still looking at).
This is the reproducer:
create table mc2ap (a int, b int) partition by range (a, b);
create table mc2ap1 partition of mc2ap for values from (1, 4) to (1, 7);
create table mc2ap2 partition of mc2ap for values from (1, 7) to (3, 8);
create table mc2ap3 partition of mc2ap for values from (4, 8) to (6, 9);
create table mc2ap_def partition of mc2ap default;
insert into mc2ap values(1,7);
explain select * from mc2ap where a <= 1;
set enable_partition_pruning=1;
select * from mc2ap where a <= 1; -- 0 rows (!)
set enable_partition_pruning=0;
select * from mc2ap where a <= 1; -- 1 row
mc2ap2 gets pruned by mistake due to an incorrectly coded loop bound.
In this scenario, before the loop, off == 0, so we never perform any
loops to look for other matching bounds. I've moved the condition
check for the loop until after nextoff has been set (according to the
inclusive variable) and breaking out the loop if nextoff is out of
bounds.
There is another similar loop in the BTGreaterStrategyNumber case. I
didn't change that one as I can't find anything wrong with it.
This problem was found using a fuzz testing script I asked Claude Code
to write. See attached.
\i partprune_fuzz.sql
SELECT * FROM pp_fuzz.regression_cases();
CALL pp_fuzz.run(100000); -- about 30 mins of processing
SELECT * FROM pp_fuzz.report();
It'll still find mismatches after applying this attached patch, but
shouldn't if you apply the fix in [1]. If it does, then there are yet
more bugs.
David
[1] https://postgr.es/m/CAON2xHO=sqdqp=z8zWkybnWp0AuvefnAi2ez2vOYWrhXB6hHWQ@mail.gmail.com
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Fix-incorrect-multi-column-RANGE-partition-prunin.patch | application/octet-stream | 4.6 KB |
| partprune_fuzz.sql | application/octet-stream | 16.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andrey Borodin | 2026-08-25 11:27:25 | Re: Randomize B-Tree page split location to avoid oscillating patterns |
| Previous Message | Yuhang Qiu | 2026-08-25 10:37:11 | Re: [PATCH] bufmgr: tighten LWLock:BufferMapping on InvalidateBuffer |