Re: cataloguing NOT NULL constraints

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)