var_is_nonnullable() fails to handle invalid NOT NULL constraints

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: var_is_nonnullable() fails to handle invalid NOT NULL constraints
Date: 2026-04-10 08:48:26
Message-ID: CAMbWs48ALW=mR0ydQ62dGS-Q+3D7WdDSh=EWDezcKp19xi=TUA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

While fixing another bug in var_is_nonnullable(), I noticed $subject.
The NOTNULL_SOURCE_SYSCACHE code path (newly added for the NOT IN to
anti-join transformation) checks pg_attribute.attnotnull, which can be
true even for invalid (NOT VALID) NOT NULL constraints.

The consequence is that query_outputs_are_not_nullable() could wrongly
conclude that a subquery's output is non-nullable, causing NOT IN to
be incorrectly converted to an anti-join.

The attached fix checks the attnullability field in the relation's
tuple descriptor instead, which correctly distinguishes valid from
invalid constraints. This is also consistent with what we do in
get_relation_notnullatts().

It could be argued that the added table_open/table_close call is a
performance concern, but I don't think so:

1. The relation is already locked by the rewriter, so
table_open(rte->relid, NoLock) is just a relcache lookup.

2. This code path is only reached when converting NOT IN to an
anti-join, and only after the outer side of the test expression has
already been proved non-nullable.

3. It is only called for relation RTEs in the subquery.

Thoughts?

- Richard

Attachment Content-Type Size
v1-0001-Fix-var_is_nonnullable-to-handle-invalid-NOT-NULL.patch application/octet-stream 8.7 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Henson Choi 2026-04-10 08:51:54 Re: Row pattern recognition
Previous Message Chao Li 2026-04-10 08:44:35 Re: Fix pgstat_database.c to honor passed database OIDs