Subject: Re: not null constraints, again

From: jian he <[email-masked]>
Date: Thu, 19 Sep 2024 11:30:00 +0800

On Tue, Sep 17, 2024 at 1:47 AM Alvaro Herrera <[email-masked]> wrote:

still digging inheritance related issues.

drop table if exists pp1,cc1, cc2;
create table pp1 (f1 int, constraint nn check (f1 > 1));
create table cc1 (f2 text, f3 int ) inherits (pp1);
create table cc2(f4 float, constraint nn check (f1 > 1)) inherits(pp1,cc1);
execute constr_meta('{pp1,cc1, cc2}');
alter table only cc2 drop constraint nn;
ERROR:  cannot drop inherited constraint "nn" of relation "cc2"

So:

drop table if exists pp1,cc1, cc2;
create table pp1 (f1 int not null);
create table cc1 (f2 text, f3 int not null no inherit) inherits (pp1);
create table cc2(f4 float, f1 int not null) inherits(pp1,cc1);
execute constr_meta('{pp1,cc1, cc2}');
alter table only cc2 drop constraint cc2_f1_not_null;

Last "alter table only cc2" should fail?
because it violates catalog-pg-constraint coninhcount description:
"The number of direct inheritance ancestors this constraint has. A
constraint with a nonzero number of ancestors cannot be dropped nor
renamed."

also
alter table only cc2 drop constraint cc2_f1_not_null;
success executed.
some pg_constraint attribute info may change.
but constraint cc2_f1_not_null still exists.

---

From: jian he <[email-masked]>
Date: Thu, 19 Sep 2024 14:40:12 +0800

still based on v3.
in src/sgml/html/ddl-partitioning.html
<<<QUOTE<<
Both CHECK and NOT NULL constraints of a partitioned table are always
inherited by all its partitions.
CHECK constraints that are marked NO INHERIT are not allowed to be
created on partitioned tables.
You cannot drop a NOT NULL constraint on a partition's column if the
same constraint is present in the parent table.
<<<QUOTE<<
we can change
"CHECK constraints that are marked NO INHERIT are not allowed to be
created on partitioned tables."
to
"CHECK and NOT NULL constraints that are marked NO INHERIT are not
allowed to be created on partitioned tables."

in sql-altertable.html we have:
<<<QUOTE<<
ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
If any of the CHECK constraints of the table being attached are marked
NO INHERIT, the command will fail; such constraints must be recreated
without the NO INHERIT clause.
<<<QUOTE<<

create table idxpart (a int constraint nn not null) partition by range (a);
create table idxpart0 (a int constraint nn not null no inherit);
alter table idxpart attach partition idxpart0 for values from (0) to (1000);

In the above sql query case,
we changed a constraint ("nn" on idxpart0) connoinherit attribute
after ATTACH PARTITION.
(connoinherit from true to false)
Do we need extra sentences to explain it?
here not-null constraint behavior seems to divert from CHECK constraint.

drop table if exists idxpart, idxpart0, idxpart1 cascade;
create table idxpart (a int) partition by range (a);
create table idxpart0 (a int primary key);
alter table idxpart attach partition idxpart0 for values from (0) to (1000);
alter table idxpart alter column a set not null;
alter table idxpart0 alter column a drop not null;
alter table idxpart0 drop constraint idxpart0_a_not_null;

"alter table idxpart0 alter column a drop not null;"
is logically equivalent to
"alter table idxpart0 drop constraint idxpart0_a_not_null;"

the first one (alter column) ERROR out,
the second success.
the second "drop constraint" should also ERROR out?
since it violates the sentence in ddl-partitioning.html
"You cannot drop a NOT NULL constraint on a partition's column if the
same constraint is present in the parent table."