public inbox for [email protected]
help / color / mirror / Atom feedFrom: Srinath Reddy Sadipiralla <[email protected]>
To: Álvaro Herrera <[email protected]>
Cc: yanliang lei <[email protected]>
Cc: [email protected]
Subject: Re: Re: Re: BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem.
Date: Mon, 26 Jan 2026 17:15:03 +0530
Message-ID: <CAFC+b6qKTes6Uz5gfjq=N0=AcOi5RD=JnRqbU-WnhN2dC21YDw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
Hi Álvaro,
On Sat, Dec 13, 2025 at 6:14 PM Álvaro Herrera <[email protected]> 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/
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Re: Re: BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem.
In-Reply-To: <CAFC+b6qKTes6Uz5gfjq=N0=AcOi5RD=JnRqbU-WnhN2dC21YDw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox