BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem.

Hi,

On Wed, Dec 10, 2025 at 8:52 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

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=#

It's an expected behavior; you can check here [0]https://www.postgresql.org/docs/18/ddl-alter.html?utm_source=chatgpt.com#DDL-ALTER-ADDING-A-CONSTRAINT,
but i am curious ,why it's not treated as normal
constraint and why we are not throwing an error
like the constraint already exists?

[0]: https://www.postgresql.org/docs/18/ddl-alter.html?utm_source=chatgpt.com#DDL-ALTER-ADDING-A-CONSTRAINT
https://www.postgresql.org/docs/18/ddl-alter.html?utm_source=chatgpt.com#DDL-ALTER-ADDING-A-CONSTRAINT

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

On Wed, Dec 10, 2025 at 9:32 PM Srinath Reddy Sadipiralla <
srinath2133@gmail.com> wrote:

why it's not treated as normal
constraint and why we are not throwing an error
like the constraint already exists?

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",
i guess the reason(s) it's a column property rather than
constraint might be an overkill, adds overhead of entries
in pg_constraint, or historical reasons, please correct me
if i am wrong, still the right way of setting this constraint "ALTER TABLE
test_null_20251210 ALTER COLUMN c1 SET NOT NULL;"
not throwing an error; i think it makes sense to throw an
error here, thoughts?

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

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)
/messages/by-id/2818249.1748706121@sss.pgh.pa.us

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.

At 2025-12-11 15:29:45, "Álvaro Herrera" <alvherre@kurilemu.de> wrote:

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?
/messages/by-id/202512110716.jpbcheffhdow@alvherre.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)

hi

I'm sorry, I didn't notice your reply. I just read your reply and I'm sorry, as I am the end user of the database and not a developer of the database, I don't know how to find commit 14e87ffa5c54.

From the perspective of the end user of the database,In PostgreSQL 18,

Step 1: After the successful execution of "create table test_null_20251210 (c1 int not null, c2 text);",
PostgreSQL 18 will automatically create a not null constraint name for column c1, and the constraint name is "test_null_20251210uc1_not_null",

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.

So, based on this situation,
I believe that there is a problem with the execution result of the Step 2 not reporting an error, which can mislead the user executing the SQL statement in Step 2.

At 2025-12-12 23:01:53, "Álvaro Herrera" <alvherre@kurilemu.de> wrote:

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)

Hi Álvaro,

On Sat, Dec 13, 2025 at 6:14 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:

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

+1 , thanks for the patch.

This is mostly straightforward to fix, as attached (though the error

message needs work),

I have reviewed the patch except the below things and
LGTM until now.

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.

I need to look further into these parts,and will update once
I have something solid.

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

Hi Álvaro,

On Sat, Dec 13, 2025 at 6:14 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:

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.

i looked into this, 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, let me show an example,

postgres=# create table test1(col1 int);
CREATE TABLE
postgres=# create table test2(col1 int not null);
CREATE TABLE
postgres=# create table child12() inherits ( test1,test2);
NOTICE: merging multiple inherited definitions of column "col1"
CREATE TABLE
postgres=# \d+ child12
Table "public.child12"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
col1 | integer | | not null | | plain |
| |
Not-null constraints:
"test2_col1_not_null" NOT NULL "col1" (inherited)
Inherits: test1,
test2
Access method: heap

postgres=# alter table test1 add constraint nn not null col1 not valid;
ERROR: mismatching constraint name "nn"
DETAIL: A not-null constraint named "test2_col1_not_null" already exists
for this column.

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.

/*
* Throw an error if the proposed constraint name doesn't match the
* existing one.
*/
+ if (is_local && name &&
strcmp(name, NameStr(conform->conname)) != 0)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("mismatching constraint name \"%s\"", name),
errdetail("A not-null constraint named \"%s\" already exists for this
column.",
NameStr(conform->conname)));

also checking how other constraints handle this case like CHECK
and found it just appends to existing constraint

postgres=# \d+ child34
Table "public.child34"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain |
| |
Check constraints:
"c" CHECK (a > 1)
"d" CHECK (a > 1)
Inherits: test3,
test4
Access method: heap

but I don't think it makes sense for NOT NULL, thoughts?

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

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 Mon, Jan 26, 2026 at 8:03 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:

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

Thanks for the pointers, will look into this angle also.

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

earlier?

sure , I will do that.

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

Hi Álvaro

On Mon, Jan 26, 2026 at 8:03 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:

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

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.

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

I've attached the updated patch.

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

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)