Content-Type: multipart/mixed; boundary="------------i1y6zyRxWbHgWd5RCcRdDl7a"
Message-ID: <d05b26fa-af54-27e1-f693-6c31590802fa@postgrespro.ru>
Date: Fri, 6 Oct 2023 18:09:45 +0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.0
Content-Language: en-US
To: pgsql-hackers@lists.postgresql.org
From: Sergei Glukhov <s.glukhov@postgrespro.ru>
Subject: Problem, partition pruning for prepared statement with IS NULL
 clause.
Archived-At: 
 <https://www.postgresql.org/message-id/d05b26fa-af54-27e1-f693-6c31590802fa%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------i1y6zyRxWbHgWd5RCcRdDl7a
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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

--------------i1y6zyRxWbHgWd5RCcRdDl7a
Content-Type: text/x-patch; charset=UTF-8;
 name="fix-partition-pruning-with-isnull.patch"
Content-Disposition: attachment;
 filename="fix-partition-pruning-with-isnull.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3BhcnRpdGlvbmluZy9wYXJ0cHJ1bmUuYyBiL3Ny
Yy9iYWNrZW5kL3BhcnRpdGlvbmluZy9wYXJ0cHJ1bmUuYwppbmRleCA3MTc5YjIyYTA1Li4w
ZmJjMmRiYTMzIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9wYXJ0aXRpb25pbmcvcGFydHBy
dW5lLmMKKysrIGIvc3JjL2JhY2tlbmQvcGFydGl0aW9uaW5nL3BhcnRwcnVuZS5jCkBAIC0z
MzgzLDEwICszMzgzLDIxIEBAIHBlcmZvcm1fcHJ1bmluZ19iYXNlX3N0ZXAoUGFydGl0aW9u
UHJ1bmVDb250ZXh0ICpjb250ZXh0LAogCQkJYm9vbAkJaXNudWxsOwogCQkJT2lkCQkJY21w
Zm47CiAKKwkJCS8qCisJCQkgKiBJUyBOVUxMIGNsYXVzZSBpcyBub3QgYWRkZWQgaW50byBh
cnJheSBvZiBFeHByU3RhdGVzCisJCQkgKiBhbmQgdGh1cyAnc3RhdGVpZHgnIGJhc2VkIG9u
ICdrZXlubycgY2Fubm90IGJlIHVzZWQgdG8gZ2V0CisJCQkgKiBFeHByU3RhdGVzIGFycmF5
IGVsZW1lbnQuIFVzZSB0aGUgaW5kZXggYmFzZWQgb24gJ252YWx1ZXMnCisJCQkgKiBpbiBv
cmRlciB0byBvYnRhaW4gcHJvcGVyIGRhdHVtIGZyb20gdGhlIGFycmF5LgorCQkJICovCisJ
CQlpbnQJCQlleHByc3RhdGVpZHggPQorCQkJCVBydW5lQ3h0U3RhdGVJZHgoY29udGV4dC0+
cGFydG5hdHRzLCBvcHN0ZXAtPnN0ZXAuc3RlcF9pZCwKKwkJCQkJCQkJIG52YWx1ZXMpOwor
CiAJCQlleHByID0gbGZpcnN0KGxjMSk7CiAJCQlzdGF0ZWlkeCA9IFBydW5lQ3h0U3RhdGVJ
ZHgoY29udGV4dC0+cGFydG5hdHRzLAogCQkJCQkJCQkJCW9wc3RlcC0+c3RlcC5zdGVwX2lk
LCBrZXlubyk7Ci0JCQlwYXJ0a2V5X2RhdHVtX2Zyb21fZXhwcihjb250ZXh0LCBleHByLCBz
dGF0ZWlkeCwKKworCQkJcGFydGtleV9kYXR1bV9mcm9tX2V4cHIoY29udGV4dCwgZXhwciwg
ZXhwcnN0YXRlaWR4LAogCQkJCQkJCQkJJmRhdHVtLCAmaXNudWxsKTsKIAogCQkJLyoKZGlm
ZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcGFydGl0aW9uX3BydW5lLm91
dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcGFydGl0aW9uX3BydW5lLm91dAppbmRl
eCBiYjEyMjNlMmIxLi5hMTZlMDhhNDA1IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNz
L2V4cGVjdGVkL3BhcnRpdGlvbl9wcnVuZS5vdXQKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9l
eHBlY3RlZC9wYXJ0aXRpb25fcHJ1bmUub3V0CkBAIC0xOTIxLDYgKzE5MjEsMTggQEAgZXhw
bGFpbiAoY29zdHMgb2ZmKSBzZWxlY3QgKiBmcm9tIGhwIHdoZXJlIChhID0gMSBhbmQgYiA9
ICdhYmNkZScpIG9yIChhID0gMiBhbmQKICAgICAgICAgIEZpbHRlcjogKCgoYSA9IDEpIEFO
RCAoYiA9ICdhYmNkZSc6OnRleHQpKSBPUiAoKGEgPSAyKSBBTkQgKGIgPSAneHh4Jzo6dGV4
dCkpIE9SICgoYSBJUyBOVUxMKSBBTkQgKGIgSVMgTlVMTCkpKQogKDcgcm93cykKIAorLS0g
dGVzdCBwYXJ0aXRpb24gcHJ1bmluZyB3aXRoIHByZXBhcmVkIHN0YXRlbWVudCBhbmQgSVMg
TlVMTCBjbGF1c2UuCitwcmVwYXJlIHN0bXQgQVMgc2VsZWN0ICogZnJvbSBocCB3aGVyZSBh
IGlzIG51bGwgYW5kIGIgPSAkMTsKK2V4cGxhaW4gKGNvc3RzIG9mZikgZXhlY3V0ZSBzdG10
KCd4eHgnKTsKKyAgICAgICAgICAgICAgICAgUVVFUlkgUExBTiAgICAgICAgICAgICAgICAg
CistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQorIEFwcGVu
ZAorICAgU3VicGxhbnMgUmVtb3ZlZDogMworICAgLT4gIFNlcSBTY2FuIG9uIGhwMiBocF8x
CisgICAgICAgICBGaWx0ZXI6ICgoYSBJUyBOVUxMKSBBTkQgKGIgPSAkMSkpCisoNCByb3dz
KQorCitkZWFsbG9jYXRlIHN0bXQ7CiAtLSB0ZXN0IHBydW5pbmcgd2hlbiBub3QgYWxsIHRo
ZSBwYXJ0aXRpb25zIGV4aXN0CiBkcm9wIHRhYmxlIGhwMTsKIGRyb3AgdGFibGUgaHAzOwpk
aWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvcGFydGl0aW9uX3BydW5lLnNxbCBi
L3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3BhcnRpdGlvbl9wcnVuZS5zcWwKaW5kZXggODNmZWQ1
NGI4Yy4uOWNhZmJkZDUzOCAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvcGFy
dGl0aW9uX3BydW5lLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9wYXJ0aXRpb25f
cHJ1bmUuc3FsCkBAIC0zNzQsNiArMzc0LDExIEBAIGV4cGxhaW4gKGNvc3RzIG9mZikgc2Vs
ZWN0ICogZnJvbSBocCB3aGVyZSBhID0gMiBhbmQgYiA9ICd4eHgnOwogZXhwbGFpbiAoY29z
dHMgb2ZmKSBzZWxlY3QgKiBmcm9tIGhwIHdoZXJlIGEgPSAxIGFuZCBiID0gJ2FiY2RlJzsK
IGV4cGxhaW4gKGNvc3RzIG9mZikgc2VsZWN0ICogZnJvbSBocCB3aGVyZSAoYSA9IDEgYW5k
IGIgPSAnYWJjZGUnKSBvciAoYSA9IDIgYW5kIGIgPSAneHh4Jykgb3IgKGEgaXMgbnVsbCBh
bmQgYiBpcyBudWxsKTsKIAorLS0gdGVzdCBwYXJ0aXRpb24gcHJ1bmluZyB3aXRoIHByZXBh
cmVkIHN0YXRlbWVudCBhbmQgSVMgTlVMTCBjbGF1c2UuCitwcmVwYXJlIHN0bXQgQVMgc2Vs
ZWN0ICogZnJvbSBocCB3aGVyZSBhIGlzIG51bGwgYW5kIGIgPSAkMTsKK2V4cGxhaW4gKGNv
c3RzIG9mZikgZXhlY3V0ZSBzdG10KCd4eHgnKTsKK2RlYWxsb2NhdGUgc3RtdDsKKwogLS0g
dGVzdCBwcnVuaW5nIHdoZW4gbm90IGFsbCB0aGUgcGFydGl0aW9ucyBleGlzdAogZHJvcCB0
YWJsZSBocDE7CiBkcm9wIHRhYmxlIGhwMzsK

--------------i1y6zyRxWbHgWd5RCcRdDl7a--