Обсуждение: BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem.

The following bug has been logged on the website:

Bug reference:      19351
Logged by:          yanliang lei
Email address:      msdnchina@163.com
PostgreSQL version: 18.1
Operating system:   Linux
Description:

postgres=# create table test_null_20251210(c1 int not null,c2 text);
CREATE TABLE
postgres=# \d+ test_null_20251210
                       数据表 "public.test_null_20251210"
 栏位 |  类型   | 校对规则 |  可空的  | 预设 |   存储   | 压缩 | 统计目标 | 描述
------+---------+----------+----------+------+----------+------+----------+------
 c1   | integer |          | not null |      | plain    |      |          |
 c2   | text    |          |          |      | extended |      |          |
Not-null constraints:
    "test_null_20251210_c1_not_null" NOT NULL "c1"
访问方法 heap

postgres=# alter table test_null_20251210 add  constraint xyzxyz not null
c1;
ALTER TABLE
<<<--- above  alter table statement should report 'not null constraint
exists',
<<<---- Maybe this problem is a bug ?
postgres=# \d+ test_null_20251210
                       数据表 "public.test_null_20251210"
 栏位 |  类型   | 校对规则 |  可空的  | 预设 |   存储   | 压缩 | 统计目标 | 描述
------+---------+----------+----------+------+----------+------+----------+------
 c1   | integer |          | not null |      | plain    |      |          |
 c2   | text    |          |          |      | extended |      |          |
Not-null constraints:
    "test_null_20251210_c1_not_null" NOT NULL "c1"
访问方法 heap

postgres=# select version();
                                      version
-----------------------------------------------------------------------------------
 PostgreSQL 18.1 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.0,
64-bit
(1 行记录)

postgres=#

On 2025-Dec-10, PG Bug reporting form wrote:

> postgres=# create table test_null_20251210(c1 int not null,c2 text);
> CREATE TABLE
> postgres=# \d+ test_null_20251210
>                        数据表 "public.test_null_20251210"
>  栏位 |  类型   | 校对规则 |  可空的  | 预设 |   存储   | 压缩 | 统计目标 | 描述
> ------+---------+----------+----------+------+----------+------+----------+------
>  c1   | integer |          | not null |      | plain    |      |          |
>  c2   | text    |          |          |      | extended |      |          |
> Not-null constraints:
>     "test_null_20251210_c1_not_null" NOT NULL "c1"
> 访问方法 heap
> 
> postgres=# alter table test_null_20251210 add  constraint xyzxyz not null
> c1;
> ALTER TABLE
> <<<--- above  alter table statement should report 'not null constraint
> exists',
> <<<---- Maybe this problem is a bug ?

Yeah, this is more-or-less intentional behavior from commit 14e87ffa5c54
-- the constraint you're trying to add is compatible with the one that
already exists, so there's no point in throwing an error.  This is
consistent with the long-standing behavior of "ALTER TABLE ... SET NOT
NULL" not throwing an error.  However, there actually is one point of
incompatibility: the constraint name doesn't match.  I ruled out
throwing an error in this case, but maybe we should.

If you try to add a NOT VALID constraint you also get no error.  (This
is because we combine both the existing definition and a proposed NOT
VALID constraint and reach the conclusion that a validated constraint is
the correct end result).

If you try to add an incompatible not-null constraint you do get an
error.  I think the only case right now is a NO INHERIT constraint.  In
the future we'll get NOT ENFORCED constraint and that should also
receive an error:

=# alter table test_null_20251210 add  constraint xyzxyz not null c1 no inherit;
ERROR:  cannot change NO INHERIT status of NOT NULL constraint "test_null_20251210_c1_not_null" on relation
"test_null_20251210"
HINT:  You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CONSTRAINT ... INHERIT.

I do note that the HINT in this case is wrong, and I'll go fix it.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"The saddest aspect of life right now is that science gathers knowledge faster
 than society gathers wisdom."  (Isaac Asimov)

On 2025-Dec-10, Srinath Reddy Sadipiralla wrote:

> ah... just after a quick code lookup, remembered that
> "not null" is treated as a column property, which is a
> flag in pg_attribute catalog table named as "attnotnull",

This is no longer true in 18.
https://www.enterprisedb.com/blog/changes-not-null-postgres-18

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Fundamental layering violations tend to bite you on tender
parts of your anatomy."                          (Tom Lane)
https://postgr.es/m/2818249.1748706121@sss.pgh.pa.us

On 2025-Dec-12, yanliang lei wrote:

> Thanks for replying .
> 
> in the postgresql 18 ，not null has “Constraint Name”，and this is a PostgreSQL 18  New Feature.
> 
> so , i think that: this problem(BUG #19351) is a bug.

Did you read my other reply?
https://www.postgresql.org/message-id/202512110716.jpbcheffhdow%40alvherre.pgsql

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended."  (Gerry Pourwelle)

On 2025-Dec-13, yanliang lei wrote:

> Step 2: The execution of 'alter table test_null_20251210 add constraint xyzxyz not null c1' did not return any
errors.
> 
> after the execution of Step 2 , the executing user of the SQL
> statement "alter table test_null_20251210 add constraint xyzxyz not
> null c1" in the Step 2 will assume (because there was no error prompt
> in the  Step 2   ) that the not null constraint has been successfully
> added to column c1, and the name of the constraint is xyzxyz.

>  ----However, in reality, based on the execution results of the SQL
>  database, the Step 2 did not actually succeed.

Yeah, that's fair -- we discussed this kind of behavior during
development and I was unsure about being strict about it, so avoided it.

This is mostly straightforward to fix, as attached (though the error
message needs work), but I hit a snag with multiple inheritance -- if
you apply this patch, you'll see failures in the pg_dump and pg_upgrade
tests.  I don't have any ideas to fix this right now, but I'll keep
thinking about it.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)