[MASSMAIL]Incorrect handling of IS [NOT] NULL quals on inheritance parents

Поиск
Список
Период
Сортировка
От Richard Guo
Тема [MASSMAIL]Incorrect handling of IS [NOT] NULL quals on inheritance parents
Дата
Msg-id CAMbWs4930gQSZmjR7aANzEapdy61gCg6z8dT-kAEYD0sYWKPdQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Incorrect handling of IS [NOT] NULL quals on inheritance parents
Список pgsql-hackers
In b262ad440e we introduced an optimization that drops IS NOT NULL quals
on a NOT NULL column, and reduces IS NULL quals on a NOT NULL column to
constant-FALSE.  I happened to notice that this is not working correctly
for traditional inheritance parents.  Traditional inheritance parents
might have NOT NULL constraints marked NO INHERIT, while their child
tables do not have NOT NULL constraints.  In such a case, we would have
problems when we have removed redundant IS NOT NULL restriction clauses
of the parent rel, as this could cause NULL values from child tables to
not be filtered out, or when we have reduced IS NULL restriction clauses
of the parent rel to constant-FALSE, as this could cause NULL values
from child tables to not be selected out.  As an example, consider

create table p (a int);
create table c () inherits (p);

alter table only p alter a set not null;

insert into c values (null);

-- The IS NOT NULL qual is droped, causing the NULL value from 'c' to
-- not be filtered out
explain (costs off) select * from p where a is not null;
       QUERY PLAN
-------------------------
 Append
   ->  Seq Scan on p p_1
   ->  Seq Scan on c p_2
(3 rows)

select * from p where a is not null;
 a
---

(1 row)

-- The IS NULL qual is reduced to constant-FALSE, causing the NULL value
-- from 'c' to not be selected out
explain (costs off) select * from p where a is null;
        QUERY PLAN
--------------------------
 Result
   One-Time Filter: false
(2 rows)

select * from p where a is null;
 a
---
(0 rows)


To fix this issue, I think we can avoid calculating notnullattnums for
inheritance parents in get_relation_info().  Meanwhile, when we populate
childrel's base restriction quals from parent rel's quals, we check if
each qual can be proven always false/true, to apply the optimization we
have in b262ad440e to each child.  Something like attached.

This can also be beneficial to partitioned tables in cases where the
parent table does not have NOT NULL constraints, while some of its child
tables do.  Previously, the optimization introduced in b262ad440e was
not applicable in this case.  With this change, the optimization can now
be applied to each child table that has the right NOT NULL constraints.

Thoughts?

Thanks
Richard
Вложения

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

Предыдущее
От: Tomas Vondra
Дата:
Сообщение: Re: Add notes to pg_combinebackup docs
Следующее
От: Dmitry Dolgov
Дата:
Сообщение: Re: broken JIT support on Fedora 40