MIME-Version: 1.0
References: <6ec30f7.19f1.19b15ec1942.Coremail.msdnchina@163.com> <202512130812.375tew7hjksb@alvherre.pgsql>
In-Reply-To: <202512130812.375tew7hjksb@alvherre.pgsql>
From: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Date: Mon, 26 Jan 2026 17:15:03 +0530
Message-ID: <CAFC+b6qKTes6Uz5gfjq=N0=AcOi5RD=JnRqbU-WnhN2dC21YDw@mail.gmail.com>
Subject: Re: Re: Re: BUG #19351: in pg18.1,when not null exists in the table ,
 and add constraint problem.
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>
Cc: yanliang lei <msdnchina@163.com>, pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000051aed40649490a85"
Archived-At: <https://www.postgresql.org/message-id/CAFC%2Bb6qKTes6Uz5gfjq%3DN0%3DAcOi5RD%3DJnRqbU-WnhN2dC21YDw%40mail.gmail.com>
Precedence: bulk

--00000000000051aed40649490a85
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi =C3=81lvaro,

On Sat, Dec 13, 2025 at 6:14=E2=80=AFPM =C3=81lvaro Herrera <alvherre@kuril=
emu.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=3D# create table test1(col1 int);
CREATE TABLE
postgres=3D# create table test2(col1 int not null);
CREATE TABLE
postgres=3D# create table child12() inherits ( test1,test2);
NOTICE:  merging multiple inherited definitions of column "col1"
CREATE TABLE
postgres=3D# \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=3D# 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)) !=3D 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=3D# \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?


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

--00000000000051aed40649490a85
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hi=C2=A0=C3=81lvaro,</di=
v><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On S=
at, Dec 13, 2025 at 6:14=E2=80=AFPM =C3=81lvaro Herrera &lt;<a href=3D"mail=
to:alvherre@kurilemu.de" target=3D"_blank">alvherre@kurilemu.de</a>&gt; wro=
te:</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">I hit a snag wit=
h multiple inheritance -- if<br>
you apply this patch, you&#39;ll see failures in the pg_dump and pg_upgrade=
<br>
tests.=C2=A0 I don&#39;t have any ideas to fix this right now, but I&#39;ll=
 keep<br>
thinking about it.<br></blockquote><div>=C2=A0</div><div>i looked into this=
, the reason for these failures was when the given name<br>for a constraint=
 for a parent table propagates to the child table because<br>of inheritance=
 the name conflicts and throws &quot;mismatching constraint name&quot;<br>e=
rror we added, let me show an example,<br><br>postgres=3D# create table tes=
t1(col1 int);<br>CREATE TABLE<br>postgres=3D# create table test2(col1 int n=
ot null);<br>CREATE TABLE<br>postgres=3D# create table child12() inherits (=
 test1,test2);<br>NOTICE: =C2=A0merging multiple inherited definitions of c=
olumn &quot;col1&quot;<br>CREATE TABLE<br>postgres=3D# \d+ child12 <br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table =
&quot;public.child12&quot;<br>=C2=A0Column | =C2=A0Type =C2=A0 | Collation =
| Nullable | Default | Storage | Compression | Stats target | Description <=
br>--------+---------+-----------+----------+---------+---------+----------=
---+--------------+-------------<br>=C2=A0col1 =C2=A0 | integer | =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plai=
n =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0| <br>Not-null constraints:<br>=C2=A0 =C2=A0 &q=
uot;test2_col1_not_null&quot; NOT NULL &quot;col1&quot; (inherited)<br>Inhe=
rits: test1,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 test2<br>Access method: =
heap<br><br>postgres=3D# alter table test1 add constraint nn not null col1 =
not valid;<br>ERROR: =C2=A0mismatching constraint name &quot;nn&quot;<br>DE=
TAIL: =C2=A0A not-null constraint named &quot;test2_col1_not_null&quot; alr=
eady exists for this column.<br><br>I think we can fix this by throwing an =
error only if this constraint was added<br>directly to the table and not th=
rough inheritance/propagation from the parent,<br>we can do this using the =
&quot;is_local&quot; flag, i have checked and all tests passed.<br><br>=C2=
=A0 /*<br>		 * Throw an error if the proposed constraint name doesn&#39;t m=
atch the<br>		 * existing one.<br>		 */<br>+ if (is_local &amp;&amp; name &=
amp;&amp;<br>			strcmp(name, NameStr(conform-&gt;conname)) !=3D 0)<br>			er=
eport(ERROR,<br>					errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),<br>=
					errmsg(&quot;mismatching constraint name \&quot;%s\&quot;&quot;, name)=
,<br>					errdetail(&quot;A not-null constraint named \&quot;%s\&quot; alre=
ady exists for this column.&quot;,<br>							 =C2=A0NameStr(conform-&gt;con=
name)));<br><br>also checking how other constraints handle this case like C=
HECK<br>and found it just appends to existing constraint<br><br>postgres=3D=
# \d+ child34 <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0Table &quot;public.child34&quot;<br>=C2=A0Column | =C2=A0T=
ype =C2=A0 | Collation | Nullable | Default | Storage | Compression | Stats=
 target | Description <br>--------+---------+-----------+----------+-------=
--+---------+-------------+--------------+-------------<br>=C2=A0a =C2=A0 =
=C2=A0 =C2=A0| integer | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 | =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0| <br>Check constraints:<br>=C2=A0 =C2=A0 &quot;c&quot; CHECK (a =
&gt; 1)<br>=C2=A0 =C2=A0 &quot;d&quot; CHECK (a &gt; 1)<br>Inherits: test3,=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 test4<br>Access method: heap<br><br>=
but I don&#39;t think it makes sense for NOT NULL, thoughts?<br><br></div><=
/div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><d=
iv dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><span style=3D"co=
lor:rgb(34,34,34)">Thanks,</span><br style=3D"color:rgb(34,34,34)"><span st=
yle=3D"color:rgb(34,34,34)">Srinath Reddy Sadipiralla</span><br style=3D"co=
lor:rgb(34,34,34)"><span style=3D"color:rgb(34,34,34)">EDB:=C2=A0</span><a =
href=3D"https://www.enterprisedb.com/" style=3D"color:rgb(17,85,204)" targe=
t=3D"_blank">https://www.enterprisedb.com/</a></div></div></div>
</div>

--00000000000051aed40649490a85--