Message-ID: <2b3a2cd5f9b6e82fba6ac45b413291b01dd252ea.camel@cybertec.at>
Subject: Re: BUG #19393: pg_upgrade fails with duplicate key violation when
 CHECK constraint named *_not_null exists
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: huseyin.d3r@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Thu, 05 Feb 2026 15:58:50 +0100
In-Reply-To: <19393-6a82427485a744cf@postgresql.org>
References: <19393-6a82427485a744cf@postgresql.org>
Content-Type: multipart/mixed; boundary="=-n/MVw3pH8/BDgkDq45bA"
User-Agent: Evolution 3.58.2 (3.58.2-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/2b3a2cd5f9b6e82fba6ac45b413291b01dd252ea.camel%40cybertec.at>
Precedence: bulk

--=-n/MVw3pH8/BDgkDq45bA
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, 2026-02-04 at 17:33 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 18.1
>=20
> While I was working on upgrading a PostgreSQL 13 cluster to PostgreSQL 18=
.1
> I encountered the following problem. pg_upgrade fails if a table has both=
 a
> NOT NULL column and a CHECK constraint with the naming pattern
> {table}_{column}_not_null.
>=20
> For example consider the following table
>=20
> -- On PostgreSQL 13
> CREATE TABLE orders (
>     id BIGSERIAL,
>     customer_id INTEGER NOT NULL,
>     CONSTRAINT orders_customer_id_not_null CHECK (customer_id IS NOT NULL=
)
> );
>
> [...]
>=20
> pg_restore: error: could not execute query: ERROR:  duplicate key value
> violates unique constraint "pg_constraint_conrelid_contypid_conname_index=
"
> DETAIL:  Key (conrelid, contypid, conname)=3D(16413, 0,
> orders_customer_id_not_null) already exists.
> Command was:
> -- For binary upgrade, must preserve pg_type oid
> SELECT
> pg_catalog.binary_upgrade_set_next_pg_type_oid('16415'::pg_catalog.oid);
>=20
>=20
> -- For binary upgrade, must preserve pg_type array oid
> SELECT
> pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16414'::pg_catalog.=
oid);
>=20
>=20
> -- For binary upgrade, must preserve pg_class oids and relfilenodes
> SELECT
> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16413'::pg_catalog.=
oid);
> SELECT
> pg_catalog.binary_upgrade_set_next_heap_relfilenode('16413'::pg_catalog.o=
id);
>=20
> CREATE TABLE "public"."orders" (
>     "id" bigint NOT NULL,
>     "customer_id" integer NOT NULL,
>     CONSTRAINT "orders_customer_id_not_null" CHECK (("customer_id" IS NOT
> NULL))
> );
>=20
> -- For binary upgrade, set heap's relfrozenxid and relminmxid
> UPDATE pg_catalog.pg_class
> SET relfrozenxid =3D '502', relminmxid =3D '1'
> WHERE oid =3D '"public"."orders"'::pg_catalog.regclass;

The bug is actually not in pg_upgrade, but in CREATE TABLE.  The attached p=
atch
fixes the problem for me by avoiding given constraint names when generating
the names for NOT NULL constraints.

Yours,
Laurenz Albe

--=-n/MVw3pH8/BDgkDq45bA
Content-Disposition: attachment;
	filename="v1-0001-Avoid-name-collision-with-NOT-NULL-constraints.patch"
Content-Type: text/x-patch;
	name="v1-0001-Avoid-name-collision-with-NOT-NULL-constraints.patch";
	charset="ISO-8859-1"
Content-Transfer-Encoding: base64

RnJvbSAxZDQyMzVjYTExOGE0NGY0YjBmYWU2YjhmNDRjNmZkN2U2YzY2MjA1IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5h
dD4KRGF0ZTogVGh1LCA1IEZlYiAyMDI2IDE1OjUyOjEwICswMTAwClN1YmplY3Q6IFtQQVRDSCB2
MV0gQXZvaWQgbmFtZSBjb2xsaXNpb24gd2l0aCBOT1QgTlVMTCBjb25zdHJhaW50cwpNSU1FLVZl
cnNpb246IDEuMApDb250ZW50LVR5cGU6IHRleHQvcGxhaW47IGNoYXJzZXQ9VVRGLTgKQ29udGVu
dC1UcmFuc2Zlci1FbmNvZGluZzogOGJpdAoKSWYgYSBDUkVBVEUgVEFCTEUgc3RhdGVtZW50IGRl
ZmluZWQgYSBjb25zdHJhaW50IHdob3NlIG5hbWUKaXMgaWRlbnRpY2FsIHRvIHRoZSBuYW1lIGdl
bmVyYXRlZCBmb3IgYSBOT1QgTlVMTCBjb25zdHJhaW50LApQb3N0Z3JlU1FMIHdvdWxkIHRocm93
IGEgdW5pcXVlIGtleSB2aW9sYXRpb24gZXJyb3Igb24KInBnX2NvbnN0cmFpbnRfY29ucmVsaWRf
Y29udHlwaWRfY29ubmFtZV9pbmRleCIuCgpUbyBmaXgsIHBhc3MgdGhlIGNvbnN0cmFpbnRzIGNy
ZWF0ZWQgYnkgQWRkUmVsYXRpb25OZXdDb25zdHJhaW50cygpCnRvIEFkZFJlbGF0aW9uTm90TnVs
bENvbnN0cmFpbnRzKCksIHNvIHRoYXQgdGhlIGxhdHRlciBjYW4gYXZvaWQKbmFtZSBjb2xsaXNp
b25zIHdpdGggdGhlIGNvbnN0cmFpbnQgbmFtZXMgaXQgZ2VuZXJhdGVzLgoKQnVnOiAjMTkzOTMK
UmVwb3J0ZWQtYnk6IEjDvHNleWluIERlbWlyIDxodXNleWluLmQzckBnbWFpbC5jb20+CkF1dGhv
cjogTGF1cmVueiBBbGJlIDxsYXVyZW56LmFsYmVAY3liZXJ0ZWMuYXQ+CkRpc2N1c3Npb246IGh0
dHBzOi8vcG9zdGdyLmVzL20vMTkzOTMtNmE4MjQyNzQ4NWE3NDRjZkBwb3N0Z3Jlc3FsLm9yZwpC
YWNrcGF0Y2gtdGhyb3VnaDogMTgKLS0tCiBzcmMvYmFja2VuZC9jYXRhbG9nL2hlYXAuYyAgICAg
ICB8IDEyICsrKysrKysrKystLQogc3JjL2JhY2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMgfCAg
NyArKysrLS0tCiBzcmMvaW5jbHVkZS9jYXRhbG9nL2hlYXAuaCAgICAgICB8ICAzICsrLQogMyBm
aWxlcyBjaGFuZ2VkLCAxNiBpbnNlcnRpb25zKCspLCA2IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdp
dCBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvaGVhcC5jIGIvc3JjL2JhY2tlbmQvY2F0YWxvZy9oZWFw
LmMKaW5kZXggNjA2NDM0ODIzY2YuLjM1Y2M2YjUwNmUwIDEwMDY0NAotLS0gYS9zcmMvYmFja2Vu
ZC9jYXRhbG9nL2hlYXAuYworKysgYi9zcmMvYmFja2VuZC9jYXRhbG9nL2hlYXAuYwpAQCAtMjg4
NSwxNCArMjg4NSwxNiBAQCBNZXJnZVdpdGhFeGlzdGluZ0NvbnN0cmFpbnQoUmVsYXRpb24gcmVs
LCBjb25zdCBjaGFyICpjY25hbWUsIE5vZGUgKmV4cHIsCiAgKiBmb3IgZWFjaCBjb2x1bW4sIGdp
dmluZyBwcmlvcml0eSB0byB1c2VyLXNwZWNpZmllZCBvbmVzLCBhbmQgc2V0dGluZwogICogaW5o
Y291bnQgYWNjb3JkaW5nIHRvIGhvdyBtYW55IHBhcmVudHMgY2F1c2UgZWFjaCBjb2x1bW4gdG8g
Z2V0IGEKICAqIG5vdC1udWxsIGNvbnN0cmFpbnQuICBJZiBhIHVzZXItc3BlY2lmaWVkIG5hbWUg
Y2xhc2hlcyB3aXRoIGFub3RoZXIKLSAqIHVzZXItc3BlY2lmaWVkIG5hbWUsIGFuIGVycm9yIGlz
IHJhaXNlZC4KKyAqIHVzZXItc3BlY2lmaWVkIG5hbWUsIGFuIGVycm9yIGlzIHJhaXNlZC4gICdl
eGlzdGluZ19jb25zdHJhaW50cycKKyAqIGlzIGEgbGlzdCBvZiBhbHJlYWR5IGRlZmluZWQgY29u
c3RyYWludHMsIHdob3NlIG5hbWVzIHNob3VsZCBiZSBhdm9pZGVkCisgKiB3aGVuIGdlbmVyYXRp
bmcgY29uc3RyYWludCBuYW1lcy4KICAqCiAgKiBSZXR1cm5zIGEgbGlzdCBvZiBBdHRyTnVtYmVy
IGZvciBjb2x1bW5zIHRoYXQgbmVlZCB0byBoYXZlIHRoZSBhdHRub3RudWxsCiAgKiBmbGFnIHNl
dC4KICAqLwogTGlzdCAqCiBBZGRSZWxhdGlvbk5vdE51bGxDb25zdHJhaW50cyhSZWxhdGlvbiBy
ZWwsIExpc3QgKmNvbnN0cmFpbnRzLAotCQkJCQkJCSAgTGlzdCAqb2xkX25vdG51bGxzKQorCQkJ
CQkJCSAgTGlzdCAqb2xkX25vdG51bGxzLCBMaXN0ICpleGlzdGluZ19jb25zdHJhaW50cykKIHsK
IAlMaXN0CSAgICpnaXZlbm5hbWVzOwogCUxpc3QJICAgKm5ubmFtZXM7CkBAIC0yOTA1LDYgKzI5
MDcsMTIgQEAgQWRkUmVsYXRpb25Ob3ROdWxsQ29uc3RyYWludHMoUmVsYXRpb24gcmVsLCBMaXN0
ICpjb25zdHJhaW50cywKIAkgKiBzeXN0ZW0tZ2VuZXJhdGVkIG5hbWUgY29uZmxpY3RzIHdlIGp1
c3QgZ2VuZXJhdGUgYW5vdGhlci4KIAkgKi8KIAlubm5hbWVzID0gTklMOworCWZvcmVhY2hfcHRy
KENvb2tlZENvbnN0cmFpbnQsIGNvbnMsIGV4aXN0aW5nX2NvbnN0cmFpbnRzKQorCXsKKwkJaWYg
KGNvbnMtPm5hbWUgIT0gTlVMTCkKKwkJCW5ubmFtZXMgPSBsYXBwZW5kKG5ubmFtZXMsIGNvbnMt
Pm5hbWUpOworCX0KKwogCWdpdmVubmFtZXMgPSBOSUw7CiAKIAkvKgpkaWZmIC0tZ2l0IGEvc3Jj
L2JhY2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMgYi9zcmMvYmFja2VuZC9jb21tYW5kcy90YWJs
ZWNtZHMuYwppbmRleCBmOTc2YzBlNWM3ZS4uY2E2OGVlOGM5OTAgMTAwNjQ0Ci0tLSBhL3NyYy9i
YWNrZW5kL2NvbW1hbmRzL3RhYmxlY21kcy5jCisrKyBiL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3Rh
YmxlY21kcy5jCkBAIC03ODEsNiArNzgxLDcgQEAgRGVmaW5lUmVsYXRpb24oQ3JlYXRlU3RtdCAq
c3RtdCwgY2hhciByZWxraW5kLCBPaWQgb3duZXJJZCwKIAlMaXN0CSAgICpyYXdEZWZhdWx0czsK
IAlMaXN0CSAgICpjb29rZWREZWZhdWx0czsKIAlMaXN0CSAgICpubmNvbHM7CisJTGlzdAkgICAq
Y29ubGlzdCA9IE5JTDsKIAlEYXR1bQkJcmVsb3B0aW9uczsKIAlMaXN0Q2VsbCAgICpsaXN0cHRy
OwogCUF0dHJOdW1iZXIJYXR0bnVtOwpAQCAtMTMzOCw4ICsxMzM5LDggQEAgRGVmaW5lUmVsYXRp
b24oQ3JlYXRlU3RtdCAqc3RtdCwgY2hhciByZWxraW5kLCBPaWQgb3duZXJJZCwKIAkgKiB1cC4K
IAkgKi8KIAlpZiAoc3RtdC0+Y29uc3RyYWludHMpCi0JCUFkZFJlbGF0aW9uTmV3Q29uc3RyYWlu
dHMocmVsLCBOSUwsIHN0bXQtPmNvbnN0cmFpbnRzLAotCQkJCQkJCQkgIHRydWUsIHRydWUsIGZh
bHNlLCBxdWVyeVN0cmluZyk7CisJCWNvbmxpc3QgPSBBZGRSZWxhdGlvbk5ld0NvbnN0cmFpbnRz
KHJlbCwgTklMLCBzdG10LT5jb25zdHJhaW50cywKKwkJCQkJCQkJCQkJdHJ1ZSwgdHJ1ZSwgZmFs
c2UsIHF1ZXJ5U3RyaW5nKTsKIAogCS8qCiAJICogRmluYWxseSwgbWVyZ2UgdGhlIG5vdC1udWxs
IGNvbnN0cmFpbnRzIHRoYXQgYXJlIGRlY2xhcmVkIGRpcmVjdGx5IHdpdGgKQEAgLTEzNDgsNyAr
MTM0OSw3IEBAIERlZmluZVJlbGF0aW9uKENyZWF0ZVN0bXQgKnN0bXQsIGNoYXIgcmVsa2luZCwg
T2lkIG93bmVySWQsCiAJICogY29sdW1ucyB0aGF0IGRvbid0IHlldCBoYXZlIGl0LgogCSAqLwog
CW5uY29scyA9IEFkZFJlbGF0aW9uTm90TnVsbENvbnN0cmFpbnRzKHJlbCwgc3RtdC0+bm5jb25z
dHJhaW50cywKLQkJCQkJCQkJCQkgICBvbGRfbm90bnVsbHMpOworCQkJCQkJCQkJCSAgIG9sZF9u
b3RudWxscywgY29ubGlzdCk7CiAJZm9yZWFjaF9pbnQoYXR0cm51bSwgbm5jb2xzKQogCQlzZXRf
YXR0bm90bnVsbChOVUxMLCByZWwsIGF0dHJudW0sIHRydWUsIGZhbHNlKTsKIApkaWZmIC0tZ2l0
IGEvc3JjL2luY2x1ZGUvY2F0YWxvZy9oZWFwLmggYi9zcmMvaW5jbHVkZS9jYXRhbG9nL2hlYXAu
aAppbmRleCA2MjRjNDE1ZGFkYi4uNmM5YWM4MTJhYTAgMTAwNjQ0Ci0tLSBhL3NyYy9pbmNsdWRl
L2NhdGFsb2cvaGVhcC5oCisrKyBiL3NyYy9pbmNsdWRlL2NhdGFsb2cvaGVhcC5oCkBAIC0xMTcs
NyArMTE3LDggQEAgZXh0ZXJuIExpc3QgKkFkZFJlbGF0aW9uTmV3Q29uc3RyYWludHMoUmVsYXRp
b24gcmVsLAogCQkJCQkJCQkJICAgY29uc3QgY2hhciAqcXVlcnlTdHJpbmcpOwogZXh0ZXJuIExp
c3QgKkFkZFJlbGF0aW9uTm90TnVsbENvbnN0cmFpbnRzKFJlbGF0aW9uIHJlbCwKIAkJCQkJCQkJ
CQkgICBMaXN0ICpjb25zdHJhaW50cywKLQkJCQkJCQkJCQkgICBMaXN0ICpvbGRfbm90bnVsbHMp
OworCQkJCQkJCQkJCSAgIExpc3QgKm9sZF9ub3RudWxscywKKwkJCQkJCQkJCQkgICBMaXN0ICpl
eGlzdGluZ19jb25zdHJhaW50cyk7CiAKIGV4dGVybiB2b2lkIFJlbGF0aW9uQ2xlYXJNaXNzaW5n
KFJlbGF0aW9uIHJlbCk7CiAKLS0gCjIuNTMuMAoK


--=-n/MVw3pH8/BDgkDq45bA--