From: Ludvig Janiuk <ludvig.janiuk@proton.me>
To: Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: 42P16 error when dropping and adding column
Date: Thu, 24 Sep 2026 14:09:09 +0000
Message-ID: <9221c3eb-2b4e-42f7-899c-61b59482a38b@proton.me> (raw)
In-Reply-To: <CA+bCEdApL1=WLxpRAOXDqBAF5WUbgvHQDSfP33NFRo+vgfB49g@mail.gmail.com>
References: <0a9b4c44-cdff-4ec8-a8fb-b3a8b59e9ba9@proton.me>
<CAHGQGwFmL6L2OPvPMCkqwrRX7QftuCiJ7RVEb8awDc26b2aVjQ@mail.gmail.com>
<CA+bCEdApL1=WLxpRAOXDqBAF5WUbgvHQDSfP33NFRo+vgfB49g@mail.gmail.com>
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 <masao.fujii@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: ludvig.janiuk@proton.me, manuelreyesbravo@gmail.com, masao.fujii@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: 42P16 error when dropping and adding column
In-Reply-To: <9221c3eb-2b4e-42f7-899c-61b59482a38b@proton.me>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox