Feedback-ID: ia2694551:Fastmail
Date: Mon, 5 Feb 2024 15:47:19 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Michael Paquier <michael@paquier.xyz>
Cc: Alexander Lakhin <exclusion@gmail.com>,
	Dean Rasheed <dean.a.rasheed@gmail.com>,
 Peter Eisentraut <peter@eisentraut.org>,
	Robert Haas <robertmhaas@gmail.com>, pgsql-hackers@lists.postgresql.org
Subject: Re: cataloguing NOT NULL constraints
Message-ID: <202402051447.wimb4xmtiiyb@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <202402050950.q367e2d6rtjs@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/202402051447.wimb4xmtiiyb%40alvherre.pgsql>
Precedence: bulk

On 2024-Feb-05, Alvaro Herrera wrote:

> So this regression test no longer fails:
> 
> create table cnn2_parted(a int primary key) partition by list (a);
> create table cnn2_part1(a int);
> alter table cnn2_parted attach partition cnn2_part1 for values in (1);

> Here, in the existing code the ALTER TABLE ATTACH fails with the error
> message that
>   ERROR:  primary key column "a" is not marked NOT NULL
> but with the patch, this no longer occurs.

I think this change is OK.  In the partition, the primary key is created
in the partition anyway (as expected) which marks the column as
attnotnull[*], and the table is scanned for presence of NULLs if there's
no not-null constraint, and not scanned if there's one.  (The actual
scan is inevitable anyway because we must check the partition
constraint).  This seems the behavior we want.

[*] This attnotnull constraint is lost if you DETACH the partition and
drop the primary key, which is also the behavior we want.


While playing with it I noticed this other behavior change from 16,

create table pa (a int primary key) partition by list (a);
create table pe (a int unique);
alter table pa attach partition pe for values in (1, null);

In 16, we get the error:
ERROR:  column "a" in child table must be marked NOT NULL
which is correct (because the PK requires not-null).  In master we just
let that through, but that seems to be a separate bug.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)