Re: 42P16 error when dropping and adding column - Mailing list pgsql-bugs

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