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

Hi Álvaro,

On Sat, Dec 13, 2025 at 6:14 PM Álvaro Herrera <alvherre(at)kurilemu(dot)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/