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

  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