42P16 error when dropping and adding column

Поиск
Список
Период
Сортировка
Искать

42P16 error when dropping and adding column

От:
Ludvig Janiuk <ludvig.janiuk@proton.me>
Дата:
Hello, it seems that between pqsl 17 and 18, the behavior of the 
following reproducer has changed:

====
CREATE TABLE reproducer(id int NOT NULL); ALTER TABLE reproducer DROP 
COLUMN "id", ADD COLUMN "id" int, ADD PRIMARY KEY("id");
====

Expected output (this is the case on psql 16 and 17):

====
CREATE TABLE
ALTER TABLE
====

Actual output (on psql (PostgreSQL) 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)):

====
CREATE TABLE
ERROR:  42P16: primary key column "id" is not marked NOT NULL
LOCATION:  index_check_primary_key, index.c:266
====

I could not find anything direcly related in the change notes 
(https://www.postgresql.org/docs/release/18.0/). There were a few lines 
related to NULL, but with my knowledge I could not be sure if they were 
relevant.

I hope you can accept my bug report, or let me know the reason why if 
this is correct behavior.

Best regards,

-- 
//Ludvig Janiuk




Re: 42P16 error when dropping and adding column

От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
Вложения:
On Thu, Sep 17, 2026 at 7:23 PM Ludvig Janiuk  wrote:
>
> Hello, it seems that between pqsl 17 and 18, the behavior of the
> following reproducer has changed:
>
> ====
> CREATE TABLE reproducer(id int NOT NULL); ALTER TABLE reproducer DROP
> COLUMN "id", ADD COLUMN "id" int, ADD PRIMARY KEY("id");
> ====
>
> Expected output (this is the case on psql 16 and 17):
>
> ====
> CREATE TABLE
> ALTER TABLE
> ====
>
> Actual output (on psql (PostgreSQL) 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)):
>
> ====
> CREATE TABLE
> ERROR:  42P16: primary key column "id" is not marked NOT NULL
> LOCATION:  index_check_primary_key, index.c:266
> ====

Thanks for the report!

This issue seems to happen because ALTER TABLE checks for an existing NOT
NULL constraint before dropping the old column. So, finding the NOT NULL
constraint on that old column causes it to skip adding one for the new
column, even though the old constraint will be removed with the old column.

The attached patch fixes this by deferring the check until after column
drops and additions have been executed. This prevents the old column's NOT
NULL constraint from being found and ensures that required NOT NULL
constraints are added to the new columns before creating the primary-key
index.

Regards,

-- 
Fujii Masao

Re: 42P16 error when dropping and adding column

От:
Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
Дата:
Вложения:
Hi Fujii-san,

Fujii Masao  wrote:
> This issue seems to happen because ALTER TABLE checks for an existing NOT
> NULL constraint before dropping the old column. So, finding the NOT NULL
> constraint on that old column causes it to skip adding one for the new
> column, even though the old constraint will be removed with the old column.

The same early check also breaks commands that do not drop the column at
all, only its not-null constraint, and v1 fixes those too.  On 18.6 each
of these fails with "primary key column "a" is not marked NOT NULL":

    CREATE TABLE t (a int NOT NULL);
    ALTER TABLE t ALTER COLUMN a DROP NOT NULL, ADD PRIMARY KEY (a);

    CREATE TABLE t (a int CONSTRAINT a_nn NOT NULL);
    ALTER TABLE t DROP CONSTRAINT a_nn, ADD PRIMARY KEY (a);

    CREATE TABLE p (a int NOT NULL) PARTITION BY RANGE (a);
    CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (0) TO (10);
    ALTER TABLE p ALTER COLUMN a DROP NOT NULL, ADD PRIMARY KEY (a);

The two DROP NOT NULL forms work on 17.11; the DROP CONSTRAINT form has
no equivalent there.

To see what else v1 changes, I ran 24 ALTER TABLE ... ADD PRIMARY KEY
scenarios on REL_18_STABLE at 459ef520d8f and master at c9c660e6ae0,
with and without v1, and compared the errors and the resulting catalog
state: the pg_constraint rows (with conislocal, coninhcount,
convalidated and connoinherit) and attnotnull.  scenarios.sql is
attached.

v1 changes exactly nine of them, all failures that go away:

  * dropping and re-adding the column: the reported case, a two-column
    key with one column replaced, a partitioned table, an inheritance
    parent, and the PRIMARY KEY subcommand written before the DROP
    COLUMN;
  * the three commands above;
  * the reported case with a row in the table, which now fails with
    "column "id" of relation "t" contains null values", as on 17.

The other fifteen give identical errors and identical catalog state with
and without v1.  Among them are plain ADD PRIMARY KEY on a partitioned
table and on an inheritance parent, ONLY on a parent whose child has or
lacks the not-null, an existing NOT VALID or NO INHERIT not-null, USING
INDEX, SET NOT NULL in the same command, an identity column, and ALTER
COLUMN TYPE in the same command.  The results are the same on both
branches.

Building a379061a22a and its parent: before it, all nine behave as they
do with v1; after it, all nine fail with "is not marked NOT NULL".  So
the commit named in the patch is the one.

v1's test covers the DROP COLUMN form only.  The attached top-up adds
the three commands above to the same place in alter_table.sql.  With v1
make check passes on master and REL_18_STABLE; with the tests but
without the tablecmds.c change, alter_table fails on all four.

Regards,
Manu

Re: 42P16 error when dropping and adding column

От:
Ludvig Janiuk <ludvig.janiuk@proton.me>
Дата:
Thank you both for responding so quickly and reviewing my report.

I'm not familiar with the Postgres bug lifecycle - is there anything 
else I need to do? File a formal bug in some tracker? Or has that 
already happened?

Best regards,
Ludvig Janiuk

On 2026-09-17 17:01, Manuel Reyes Bravo wrote:
> Hi Fujii-san,
>
> Fujii Masao  wrote:
>> This issue seems to happen because ALTER TABLE checks for an existing NOT
>> NULL constraint before dropping the old column. So, finding the NOT NULL
>> constraint on that old column causes it to skip adding one for the new
>> column, even though the old constraint will be removed with the old column.
> The same early check also breaks commands that do not drop the column at
> all, only its not-null constraint, and v1 fixes those too.  On 18.6 each
> of these fails with "primary key column "a" is not marked NOT NULL":
>
>      CREATE TABLE t (a int NOT NULL);
>      ALTER TABLE t ALTER COLUMN a DROP NOT NULL, ADD PRIMARY KEY (a);
>
>      CREATE TABLE t (a int CONSTRAINT a_nn NOT NULL);
>      ALTER TABLE t DROP CONSTRAINT a_nn, ADD PRIMARY KEY (a);
>
>      CREATE TABLE p (a int NOT NULL) PARTITION BY RANGE (a);
>      CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (0) TO (10);
>      ALTER TABLE p ALTER COLUMN a DROP NOT NULL, ADD PRIMARY KEY (a);
>
> The two DROP NOT NULL forms work on 17.11; the DROP CONSTRAINT form has
> no equivalent there.
>
> To see what else v1 changes, I ran 24 ALTER TABLE ... ADD PRIMARY KEY
> scenarios on REL_18_STABLE at 459ef520d8f and master at c9c660e6ae0,
> with and without v1, and compared the errors and the resulting catalog
> state: the pg_constraint rows (with conislocal, coninhcount,
> convalidated and connoinherit) and attnotnull.  scenarios.sql is
> attached.
>
> v1 changes exactly nine of them, all failures that go away:
>
>    * dropping and re-adding the column: the reported case, a two-column
>      key with one column replaced, a partitioned table, an inheritance
>      parent, and the PRIMARY KEY subcommand written before the DROP
>      COLUMN;
>    * the three commands above;
>    * the reported case with a row in the table, which now fails with
>      "column "id" of relation "t" contains null values", as on 17.
>
> The other fifteen give identical errors and identical catalog state with
> and without v1.  Among them are plain ADD PRIMARY KEY on a partitioned
> table and on an inheritance parent, ONLY on a parent whose child has or
> lacks the not-null, an existing NOT VALID or NO INHERIT not-null, USING
> INDEX, SET NOT NULL in the same command, an identity column, and ALTER
> COLUMN TYPE in the same command.  The results are the same on both
> branches.
>
> Building a379061a22a and its parent: before it, all nine behave as they
> do with v1; after it, all nine fail with "is not marked NOT NULL".  So
> the commit named in the patch is the one.
>
> v1's test covers the DROP COLUMN form only.  The attached top-up adds
> the three commands above to the same place in alter_table.sql.  With v1
> make check passes on master and REL_18_STABLE; with the tests but
> without the tablecmds.c change, alter_table fails on all four.
>
> Regards,
> Manu

-- 
//Ludvig Janiuk




Re: 42P16 error when dropping and adding column

От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Hi Ludvig,

Ludvig Janiuk  wrote:
> I'm not familiar with the Postgres bug lifecycle - is there anything
> else I need to do? File a formal bug in some tracker? Or has that
> already happened?

Nothing else is needed from you.  PostgreSQL has no separate bug
tracker: this thread on pgsql-bugs is the bug report, and it is already
archived where the committers look.

Fujii posted a patch (v1) in this thread, and it was tested against
master and REL_18_STABLE.  As of today it is not committed yet.  Once a
committer pushes it, the commit message usually links back to this
thread, and a fix for an 18 regression like this one normally goes into
the next 18.x minor release, where it shows up in the release notes.

If you want to follow it, watching this thread is enough.  Thanks for
the clear report with a reproducer, it made the bisect straightforward.

Regards,
Manu


Re: 42P16 error when dropping and adding column

От:
Ludvig Janiuk <ludvig.janiuk@proton.me>
Дата:
Okay, thank you.

Best regards,
Ludvig Janiuk

On 2026-09-25 02:50, Manu wrote:
> Hi Ludvig,
>
> Ludvig Janiuk  wrote:
>> I'm not familiar with the Postgres bug lifecycle - is there anything
>> else I need to do? File a formal bug in some tracker? Or has that
>> already happened?
> Nothing else is needed from you.  PostgreSQL has no separate bug
> tracker: this thread on pgsql-bugs is the bug report, and it is already
> archived where the committers look.
>
> Fujii posted a patch (v1) in this thread, and it was tested against
> master and REL_18_STABLE.  As of today it is not committed yet.  Once a
> committer pushes it, the commit message usually links back to this
> thread, and a fix for an 18 regression like this one normally goes into
> the next 18.x minor release, where it shows up in the release notes.
>
> If you want to follow it, watching this thread is enough.  Thanks for
> the clear report with a reproducer, it made the bisect straightforward.
>
> Regards,
> Manu

-- 
//Ludvig Janiuk




Re: 42P16 error when dropping and adding column

От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
Вложения:
On Fri, Sep 18, 2026 at 12:01 AM Manuel Reyes Bravo
 wrote:
> v1's test covers the DROP COLUMN form only.  The attached top-up adds
> the three commands above to the same place in alter_table.sql.

Thanks! Attached is v2 patch, which includes your tests.

While working with the v1 patch, I found that the following DDL failed even
though it succeeds without the patch. v2 fixes this regression introduced by
v1.

CREATE TABLE t (id int NOT NULL);
ALTER TABLE t
  ALTER COLUMN id DROP NOT NULL,
  ADD PRIMARY KEY (id),
  ADD CONSTRAINT hoge NOT NULL id;

Regards,

-- 
Fujii Masao

Re: 42P16 error when dropping and adding column

От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Hi Fujii-san,

> Thanks! Attached is v2 patch, which includes your tests.
> While working with the v1 patch, I found that the following DDL failed
> [...] v2 fixes this regression introduced by v1.

I ran v2 through a differential of 32 ALTER TABLE cases on master
(a5447a2deac) and REL_18_STABLE, cassert builds, comparing each build
against unpatched master and REL_18: the 24 from the v1 review plus 8
around a separate named NOT NULL combined with ADD PRIMARY KEY.

v2 fixes the reported case and the seven other 42P16 forms in the set,
each with the expected catalog state (the primary key and an
auto-created NOT NULL).  The regression you caught,

    ALTER TABLE t ALTER COLUMN id DROP NOT NULL,
      ADD PRIMARY KEY (id), ADD CONSTRAINT hoge NOT NULL id;

fails on v1 with "cannot create not-null constraint" and succeeds on
v2, as do the drop-column and partitioned variants of it.

No case in the set errors on v2 while succeeding on unpatched master or
REL_18.  The scenarios where a primary key still cannot be added (ONLY
on an inheritance parent, a column that already holds a null row, a
redundant named NOT NULL on a column that already has one) error
identically on all three.  master and REL_18 give the same result on
every case, and make check passes on both.

Thanks for folding in the tests.

Regards,
Manu


Re: 42P16 error when dropping and adding column

От:
Fujii Masao <masao.fujii@gmail.com>
Дата:
On Tue, Sep 29, 2026 at 2:55 AM Manu  wrote:
> No case in the set errors on v2 while succeeding on unpatched master or
> REL_18.  The scenarios where a primary key still cannot be added (ONLY
> on an inheritance parent, a column that already holds a null row, a
> redundant named NOT NULL on a column that already has one) error
> identically on all three.  master and REL_18 give the same result on
> every case, and make check passes on both.

Thanks for the test! I've pushed the patch.

Regards,


-- 
Fujii Masao


FAQ