Обсуждение: 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)

Hello,

On 2026-Jan-26, Srinath Reddy Sadipiralla wrote:

> i looked into this,

Thank you!

> the reason for these failures was when the given name for a constraint
> for a parent table propagates to the child table because of
> inheritance the name conflicts and throws "mismatching constraint
> name" error we added,

Right, that's what I saw.

> I think we can fix this by throwing an error only if this constraint
> was added directly to the table and not through
> inheritance/propagation from the parent, we can do this using the
> "is_local" flag, i have checked and all tests passed.

Hmm, I'm not opposed to this; does it change any other behavior?  I
think it's important to see whether there are other corner cases that
would react to this behavior change.  For example, what would happen if
two existing parents have a not-null constraint on the same column?  Is
there a change for combined LIKE and regular inheritance?  I think we
should have reasonable reactions to each of those scenarios:

create table parent (a int not null);
create table parent2 (a int not null);

create table child1 () inherits (parent, parent2);
create table child2 () inherits (parent2, parent);
create table child3 (not null a) inherits (parent2, parent);

create table child4 (like parent) inherits (parent2);
-- and so on as your imagination allows



Would you be able to send a patch based on this idea and what I sent
earlier?  

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Las navajas y los monos deben estar siempre distantes"   (Germán Poo)

On 2026-Feb-01, Srinath Reddy Sadipiralla wrote:

> as you have suggested i have looked whether it effects the other behaviour
> ,during table creation with not null constraints i observed that flow
> doesn't
> touch the AdjustNotNullInheritance where we added the error message,
> When running CREATE TABLE, the standard NOT NULL merging logic is
> handled by DefineRelation -> AddRelationNotNullConstraints. This function
> explicitly handles the "Constraint Selection" logic (prioritizing the
> Child's constraint if present, otherwise defaulting to the 1st
> parent's constraint), please correct me if I totally understood your
> concerns wrong here.

Okay, it should be all good then.  I noticed that some of the changes in
the patch were unnecessary; I had added them transiently to cover the
inheritance case while investigating, but since the real fix only
affects directly specified constraints and doesn't touch inherited ones,
we can remove them.  In particular this reverts the unpleasant change
that was going to occur for inherited constraints, which was quite bulky
in the regression tests.

I also reworded the message to be closer to our guidelines and to other
nearby messages, and expanded the code comment that described why we're
doing this check.

Here's the patch in v3, which I intend to push tomorrow morning to both
18 and master.  (It backpatches cleanly).  For 18 it will mean an ABI
break due to the change to AdjustNotNullInheritance()'s signature,
requiring a touch to .abi-compliance-history as well, but that comes
later.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

On 2026-Feb-02, Álvaro Herrera wrote:

> Here's the patch in v3, which I intend to push tomorrow morning to both
> 18 and master.  (It backpatches cleanly).  For 18 it will mean an ABI
> break due to the change to AdjustNotNullInheritance()'s signature,
> requiring a touch to .abi-compliance-history as well, but that comes
> later.

Pushed.  I'm now going to wait for the failure in baza.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Computing is too important to be left to men." (Karen Spärck Jones)