public inbox for [email protected]  

help / color / mirror / Atom feed

From: SATYANARAYANA NARLAPURAM <[email protected]>
To: Richard Guo <[email protected]>
Cc: Pg Hackers <[email protected]>
Subject: Re: var_is_nonnullable() fails to handle invalid NOT NULL constraints
Date: Sun, 12 Apr 2026 02:33:19 -0700
Message-ID: <CAHg+QDcXStdF1m4ZbgT5qnRL_BruLSyBDGgRsibEFutKM2Jqxw@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs48ALW=mR0ydQ62dGS-Q+3D7WdDSh=EWDezcKp19xi=TUA@mail.gmail.com>
References: <CAMbWs48ALW=mR0ydQ62dGS-Q+3D7WdDSh=EWDezcKp19xi=TUA@mail.gmail.com>

Hi RIchard,

On Fri, Apr 10, 2026 at 1:48 AM Richard Guo <[email protected]> wrote:

> 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().
>

I tested this patch against the current HEAD (155c03ee) and it looks good.
Build & tests: Applies cleanly, compiles without warnings, all 247
regression tests
pass including the new subselect test case. Reproduced the bug before the
patch
and verified it is fixed after the patch.


> 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?
>

Looks like it needs to perform table_open/table_close multiple times
depending upon
the number of output columns? I don't see it as a major concern but let
others comment.

Thanks,
Satya

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: var_is_nonnullable() fails to handle invalid NOT NULL constraints
  In-Reply-To: <CAHg+QDcXStdF1m4ZbgT5qnRL_BruLSyBDGgRsibEFutKM2Jqxw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox