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

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Incorrect handling of IS [NOT] NULL quals on inheritance parents
Date: 2024-04-09 09:54:41
Message-ID: CAMbWs4930gQSZmjR7aANzEapdy61gCg6z8dT-kAEYD0sYWKPdQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Attachment Content-Type Size
v1-0001-Fix-handling-of-IS-NOT-NULL-quals-on-inheritance-parents.patch application/octet-stream 11.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Dolgov 2024-04-09 10:05:21 Re: broken JIT support on Fedora 40
Previous Message Tomas Vondra 2024-04-09 09:44:57 Re: Add notes to pg_combinebackup docs