Horton

Thread

Commits

  1. Prevent setting a column as identity if its not-null constraint is invalid

  1. fix NOT VALID NOT NULL with ALTER COLUMN SET IDENTITY

    jian he <jian.universality@gmail.com> — 2025-09-03T08:30:54Z

    hi.

The attached patch makes the last two statements below fail.
CREATE TABLE notnull_tbl1 (a int, b int);
INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok
ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS
IDENTITY; --error
ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID,
  ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; --error


but in another case,
CREATE TABLE notnull_tbl1 (a int, b int);
INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;

I am not so sure the below two statements should error out or not?
ALTER TABLE notnull_tbl1 ADD COLUMN c int GENERATED BY DEFAULT AS
IDENTITY, ADD CONSTRAINT nn NOT NULL c NOT VALID;
ALTER TABLE notnull_tbl1 ADD COLUMN d serial, ADD CONSTRAINT nn NOT
NULL c NOT VALID;

  2. Re: fix NOT VALID NOT NULL with ALTER COLUMN SET IDENTITY

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-11-03T13:33:13Z

    On 2025-Sep-03, jian he wrote:

> hi.
> 
> The attached patch makes the last two statements below fail.
> CREATE TABLE notnull_tbl1 (a int, b int);
> INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
> ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok
> ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS
> IDENTITY; --error

Yeah, I think an error here is correct.

 ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID,
   ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; --error

I don't understand the difference between this test case and the
previous one ... I mean, by this point, the constraint already has a
not-null constraint, so asking to add another one does nothing.


> but in another case,
>
> CREATE TABLE notnull_tbl1 (a int, b int);
> INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
> ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
>
> I am not so sure the below two statements should error out or not?
> ALTER TABLE notnull_tbl1 ADD COLUMN c int GENERATED BY DEFAULT AS
> IDENTITY, ADD CONSTRAINT nn NOT NULL c NOT VALID;
> ALTER TABLE notnull_tbl1 ADD COLUMN d serial, ADD CONSTRAINT nn NOT NULL c NOT VALID;

Hmm.  Here we add the column as identity or serial, which marks it as
not-null, and try to add a not-valid constraint to it on top.  This
results in a valid constraint, so I think it's okay.  (A new column
cannot have existing violating data.)

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

  3. Re: fix NOT VALID NOT NULL with ALTER COLUMN SET IDENTITY

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-11-03T14:59:31Z

    Pushed now to 18 and master.  Thanks.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Once again, thank you and all of the developers for your hard work on
PostgreSQL.  This is by far the most pleasant management experience of
any database I've worked on."                             (Dan Harris)
http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php