Horton

Re: not null constraints, again

jian he <jian.universality@gmail.com>

View thread View on postgr.es
From: jian he <jian.universality@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>, Tender Wang <tndrwang@gmail.com>
Date: 2024-10-10T06:30:32Z
Lists: pgsql-hackers 
tricky case:
drop table if exists part, part0 cascade;
create table part (a int not null) partition by range (a);
create table part0 (a int primary key);
alter table part attach partition part0 for values from (0) to (1000);
alter table ONLY part add primary key(a);
alter table ONLY part drop constraint part_a_not_null;
-- alter table ONLY part alter column a drop not null;


Now we are in a state where a partitioned
table have a primary key but doesn't have a not-null constraint for it.

select  indisunique, indisprimary, indimmediate,indisvalid
from    pg_index
where   indexrelid = 'part_pkey'::regclass;

shows this primary key index is invalid.

but
select conname,contype,convalidated
from pg_constraint where conname = 'part_pkey';

shows this primary key constraint is valid.

Commits

  1. Suppress "may be used uninitialized" warnings from older compilers.

  2. Elide not-null constraint checks on child tables during PK creation

  3. Remove unnecessary code to handle CONSTR_NOTNULL

  4. Silence compilers about extractNotNullColumn()

  5. Add pg_constraint rows for not-null constraints