Re: not null constraints, again
jian he <jian.universality@gmail.com>
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-11-07T05:59:34Z
Lists: pgsql-hackers
sql-altertable.html
<varlistentry id="sql-altertable-desc-set-drop-not-null">
<term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
<listitem>
<para>
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
<para>
If this table is a partition, one cannot perform <literal>DROP
NOT NULL</literal>
on a column if it is marked <literal>NOT NULL</literal> in the parent
table. To drop the <literal>NOT NULL</literal> constraint from all the
partitions, perform <literal>DROP NOT NULL</literal> on the parent
table.
</para>
Now this will be slightly inaccurate.
drop table if exists part, part0 cascade;
create table part (a int not null) partition by range (a);
create table part0 (a int not null);
alter table part attach partition part0 for values from (0) to (1000);
alter table ONLY part0 add primary key(a);
alter table part alter column a drop not null;
as the example shows that part0 not-null constraint is still there.
that means:
perform <literal>DROP NOT NULL</literal> on the parent table
will not drop the <literal>NOT NULL</literal> constraint from all partitions.
so we need rephrase the following sentence:
To drop the <literal>NOT NULL</literal> constraint from all the
partitions, perform <literal>DROP NOT NULL</literal> on the parent
table.
to address this kind of corner case?
Commits
-
Suppress "may be used uninitialized" warnings from older compilers.
- fc5e966f73f0 18.0 landed
-
Elide not-null constraint checks on child tables during PK creation
- 11ff192b5bb7 18.0 landed
-
Remove unnecessary code to handle CONSTR_NOTNULL
- 5b291d1c9c09 18.0 landed
-
Silence compilers about extractNotNullColumn()
- ff239c3bf4e8 18.0 landed
-
Add pg_constraint rows for not-null constraints
- 14e87ffa5c54 18.0 landed