Problem, partition pruning for prepared statement with IS NULL clause.

Поиск
Список
Период
Сортировка
От Sergei Glukhov
Тема Problem, partition pruning for prepared statement with IS NULL clause.
Дата
Msg-id d05b26fa-af54-27e1-f693-6c31590802fa@postgrespro.ru
обсуждение исходный текст
Ответы Re: Problem, partition pruning for prepared statement with IS NULL clause.  (Bruce Momjian <bruce@momjian.us>)
Re: Problem, partition pruning for prepared statement with IS NULL clause.  (David Rowley <dgrowleyml@gmail.com>)
Список pgsql-hackers
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

Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Build the docs if there are changes in docs and don't run other tasks if the changes are only in docs
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Two Window aggregate node for logically same over clause