MIME-Version: 1.0
References: <19393-6a82427485a744cf@postgresql.org> <2b3a2cd5f9b6e82fba6ac45b413291b01dd252ea.camel@cybertec.at>
 <61c535617992fff830961ecc09a9c20096bc1f36.camel@cybertec.at>
In-Reply-To: <61c535617992fff830961ecc09a9c20096bc1f36.camel@cybertec.at>
From: =?UTF-8?Q?H=C3=BCseyin_Demir?= <huseyin.d3r@gmail.com>
Date: Mon, 9 Feb 2026 07:24:42 +0100
Message-ID: <CAB5wL7aPZ_GPEZkMtkcnQjmv_uMxjJZmsrCf9onDCJGL_NUtTg@mail.gmail.com>
Subject: Re: BUG #19393: pg_upgrade fails with duplicate key violation when
 CHECK constraint named *_not_null exists
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006d3735064a5e32fd"
Archived-At: <https://www.postgresql.org/message-id/CAB5wL7aPZ_GPEZkMtkcnQjmv_uMxjJZmsrCf9onDCJGL_NUtTg%40mail.gmail.com>
Precedence: bulk

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

Hi Laurenz,

Thanks for the patch and LGTM for PostgreSQL 18.

I tried to create following table on PG18

benchmark=3D# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
ERROR: duplicate key value violates unique constraint
"pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=3D(16385, 0,
two_not_null_constraints_col_not_null) already exists.

In PG17 I was able to create the table.

benchmark=3D# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
CREATE TABLE
benchmark=3D# SELECT conname, contype FROM pg_constraint
WHERE conrelid =3D 'two_not_null_constraints'::regclass
ORDER BY conname;
DROP TABLE two_not_null_constraints;
-[ RECORD 1 ]----------------------------------
conname | two_not_null_constraints_col_not_null
contype | c


One question during the tests should we confirm the output of pg_constraint
table ? It would make sense during the tests but the current test is also
good to proceed.

benchmark=3D# SELECT conname, contype FROM pg_constraint
WHERE conrelid =3D 'two_not_null_constraints'::regclass
ORDER BY conname;
conname | contype
----------------------------------------+---------
two_not_null_constraints_col_not_null | n
two_not_null_constraints_col_not_null1 | c
(2 rows)

Laurenz Albe <laurenz.albe@cybertec.at>, 5 =C5=9Eub 2026 Per, 17:52 tarihin=
de
=C5=9Funu yazd=C4=B1:

> On Thu, 2026-02-05 at 15:58 +0100, I wrote:
> > The bug is actually not in pg_upgrade, but in CREATE TABLE.  The
> attached patch
> > fixes the problem for me by avoiding given constraint names when
> generating
> > the names for NOT NULL constraints.
>
> ... and here is v2, including a regression test.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr"><div>Hi Laurenz, </div><div><br></div><div>Thanks for the =
patch and LGTM for PostgreSQL 18. </div><div><br></div><div>I tried to crea=
te following table on PG18</div><div><br></div><div style=3D"box-sizing:bor=
der-box;padding:8px;color:rgb(51,51,51);border-radius:4px;background-color:=
rgb(251,250,248);border:1px solid rgba(0,0,0,0.15)"><div>benchmark=3D# CREA=
TE TABLE two_not_null_constraints (</div><div>     col integer NOT NULL,</d=
iv><div>     CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS=
 NOT NULL)</div><div>  );</div><div>ERROR:  duplicate key value violates un=
ique constraint &quot;pg_constraint_conrelid_contypid_conname_index&quot;</=
div><div>DETAIL:  Key (conrelid, contypid, conname)=3D(16385, 0, two_not_nu=
ll_constraints_col_not_null) already exists.</div></div><div><br></div><div=
>In PG17 I was able to create the table.</div><div><br></div><div style=3D"=
box-sizing:border-box;padding:8px;color:rgb(51,51,51);border-radius:4px;bac=
kground-color:rgb(251,250,248);border:1px solid rgba(0,0,0,0.15)"><div>benc=
hmark=3D#  CREATE TABLE two_not_null_constraints (</div><div>    col intege=
r NOT NULL,</div><div>    CONSTRAINT two_not_null_constraints_col_not_null =
CHECK (col IS NOT NULL)</div><div> );</div><div>CREATE TABLE</div><div>benc=
hmark=3D#  SELECT conname, contype FROM pg_constraint</div><div>   WHERE co=
nrelid =3D &#39;two_not_null_constraints&#39;::regclass</div><div>   ORDER =
BY conname;</div><div> DROP TABLE two_not_null_constraints;</div><div>-[ RE=
CORD 1 ]----------------------------------</div><div>conname | two_not_null=
_constraints_col_not_null</div><div>contype | c</div></div><div><br></div><=
div><br></div><div>One question during the tests should we confirm the outp=
ut of <code>pg_constraint</code> table ? It would make sense during the tes=
ts but the current test is also good to proceed. </div><div><br></div><div =
style=3D"box-sizing:border-box;padding:8px;color:rgb(51,51,51);border-radiu=
s:4px;background-color:rgb(251,250,248);border:1px solid rgba(0,0,0,0.15)">=
<div>benchmark=3D# SELECT conname, contype FROM pg_constraint</div><div>   =
 WHERE conrelid =3D &#39;two_not_null_constraints&#39;::regclass</div><div>=
    ORDER BY conname;</div><div>                conname                 | c=
ontype </div><div>----------------------------------------+---------</div><=
div> two_not_null_constraints_col_not_null  | n</div><div> two_not_null_con=
straints_col_not_null1 | c</div><div>(2 rows)</div></div></div><br><div cla=
ss=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_at=
tr">Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.al=
be@cybertec.at</a>&gt;, 5 =C5=9Eub 2026 Per, 17:52 tarihinde =C5=9Funu yazd=
=C4=B1:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, =
2026-02-05 at 15:58 +0100, I wrote:<br>
&gt; The bug is actually not in pg_upgrade, but in CREATE TABLE.=C2=A0 The =
attached patch<br>
&gt; fixes the problem for me by avoiding given constraint names when gener=
ating<br>
&gt; the names for NOT NULL constraints.<br>
<br>
... and here is v2, including a regression test.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--0000000000006d3735064a5e32fd--