Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wVUB7-001zqN-26 for pgsql-bugs@arkaria.postgresql.org; Fri, 05 Jun 2026 13:01:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wVUB6-00CW1u-1f for pgsql-bugs@arkaria.postgresql.org; Fri, 05 Jun 2026 13:01:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wVUB5-00CW1m-30 for pgsql-bugs@lists.postgresql.org; Fri, 05 Jun 2026 13:01:36 +0000 Received: from mail-lf1-x12b.google.com ([2a00:1450:4864:20::12b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wVUB3-00000001EvR-15ux for pgsql-bugs@lists.postgresql.org; Fri, 05 Jun 2026 13:01:34 +0000 Received: by mail-lf1-x12b.google.com with SMTP id 2adb3069b0e04-5aa5f11deb2so2062294e87.3 for ; Fri, 05 Jun 2026 06:01:33 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1780664490; cv=none; d=google.com; s=arc-20240605; b=hlctQXj3VK8ZIwk07wRZhGxkn/TdF6DqTSCS0F0ZCHngsR5Yd46zxH+cRXKCL22mxx L3UeERONllcPRUKrKPeXZZFrhJ/c/y57TUxMnKzsY3dZ/v5e/2JKrKQhKZ8wIv3nnq8B Qcm2SlT5rafZtNBZdXMrBDERGNQgBSGg1+ihJWpkt6nc9aAd5vC87+IXhmXrkjOb6WmN +Pp97SyZoPw/20NSl7bslZllF4jp+PRe369IhiOtiuIxGgDBZqqdplG3IO4LMLxfV0u2 nhfuxXmHt2f/16BWYcVCc2mNxdzDWZCKzsfRUVKk3EJv+EQxAhSsWm87SD5HmY/YX/8Q xF9Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=RrWd7QhsnYdg6lxaSuakvu5izEwuDLLKPpAix3m+2DA=; fh=R0daaj6cpxsCgIZ6gMXGF6QGpCMxMBReOAL0c7l3/jk=; b=iXb4Hs/PPj6BRAQ9LZ7gl0VUqyhGsDgP1qM5NpK7cgScqNTsiR6flmL/NKRsz07Nsx prXufIWxCyMXPfLlorT1GwQMbrkhN0qaGnaOA7xj+k8ifLG8MIIMHgEb9aaWY6wKJifB gg7Z66MquwR30XZVJTJNFCfznjyElPL55BEHKnC6D5gJKVnFOYZheItOuNBNfpU+TDQf 4BPHx3kb/SvHiI14gTrfoNOKNUGuT5WvaBUFbkrB4bJvIS3H/m0lDjgobx35EkV6Kjf+ 9xyOeETT7EVZWgOaQI4sAnNz18ETB1YszZ+8AHdfGCJ6jSPa7MA0GtJCnvmxKmByLlLm 05XQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1780664490; x=1781269290; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=RrWd7QhsnYdg6lxaSuakvu5izEwuDLLKPpAix3m+2DA=; b=nQPkKaN/cbO6wl0QJANEm7CrGFxdW0xJZIEj2rIHSNQM2LepsDS1G8zsJRJONkFhIj GR3ELmEglxu0IxM/naVjYNMGZz8SAeox4UITwDvsfOeYwtMorU5ndy1BzC5sx5xHkYuL CXA+Du/1X6VbHgqXpQuG834epCGlib2BbuHynbPll1dO8U570SEINQeaD0J5FnAMgl56 Ao7UXNgGU7+u1aAzG8laI1ELxtrXBpC+rZqpNihUnNZ/Je9dg1nBzrZxFSUDh1RK2q/E 2kK78oBX75aYhElD9f7/CGYlX77Lgc0fdztO2OyQm/BIZ62N3hpl2jQEEtxYXIKUpy6W MjOg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1780664490; x=1781269290; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=RrWd7QhsnYdg6lxaSuakvu5izEwuDLLKPpAix3m+2DA=; b=F4IesKSybqvtutrPMHKia4RCcmEVnDie16FpRNuCjTScwONA8IEKhqmgF0WnJEg+Vg zZ5SRQyJRVMfetJsS/DcQZ8Na5vifTrCAviGCd30KfinWpaYysAScGMRgCGodRX+St9/ 45wUoktpLxnzSHv6Z90nM4PQuC0VfzrkRhZTUXmOqBy0VmEb7fNq/0a4bDLom8JxFF03 yZ+tPmhriam+5T11gEgc+wEbVtUXGkhhudpt4k84/ClhOFqvjymEy55LgECPCIHuQej6 ZSeAblsCSIMR6mVlziWbfdrmy95UKp10NhhQJ9FmxuHZWjhEOawNBCxoyxKps+zYlFDT 2lfw== X-Gm-Message-State: AOJu0Yy2mIU26Y4Tf6tsYCOKLE8CtgdAEvWEHaUtNZ79G9FG6RQ0peRM ADlMmRbL6rU2by/QCHGthrUiakNrdW+nckYGStYuCNm6bv7FszvsLYoQhaihhUqTJNLYDKjO1Qm O8Ht9K+lNFOE5zrWnKuf4nYh5C8K3Xhg= X-Gm-Gg: Acq92OEhoozo3PuOtr24zUpijIMINJkroWe87VSpBk5UOlJ2ESiFtehnNuZHKdOU+zr FbpOwnDoYNfyGsmtQ2jL9vnVb8ayUuaiOk/UctU2QDhXwDZRaTIuw7tmWCrRiW7MZfzirZH34p1 71iWA1K5l4+pq6lprEIpOgHUzvrgpaZzC3iTIUWquDXslGdJDXj8H4E8As7K/ym4l2Va3zKza2Y EwTftR4jD/4k8xLkA22SOiSq0yK0QyBoXpL8rMvabySe+aZCp8WulyaD/6f7GkLi9s03JkrGEKL WMeCSa7h/aiCtJ3AIEs8uAgyyv1KZ59mnymZPraa7m32qqzdwJ9b47+9mW5CGn/dX56ueDW5zgB lTovnWg== X-Received: by 2002:a05:6512:2903:b0:5aa:659b:2a95 with SMTP id 2adb3069b0e04-5aa87bc2672mr857468e87.36.1780664489893; Fri, 05 Jun 2026 06:01:29 -0700 (PDT) MIME-Version: 1.0 References: <19507-78eec77213b5284f@postgresql.org> <8690FCD2-B96C-4C0B-BFAC-94E9383A47AA@Outlook.com> In-Reply-To: <8690FCD2-B96C-4C0B-BFAC-94E9383A47AA@Outlook.com> From: Marko Grujic Date: Fri, 5 Jun 2026 15:01:18 +0200 X-Gm-Features: AVVi8CciT5sOlwT8AlFzvVaGUYHyt8B1BuZ2hpYrklDk4wa_hYBnr9c6DR6N7UE Message-ID: Subject: Re: BUG #19507: Auto-named partition table constraint conflicts To: Chengpeng Yan Cc: "pgsql-bugs@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000062635c06538142fe" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000062635c06538142fe Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Chengpeng, Thanks for the heads up. > My current plan is to preserve the existing behavior for explicitly named constraints, but make automatically generated names avoid conflicts that would be hit during recursive propagation. In other words, when considering a generated candidate name for the parent, the code should also consider whether using that name throughout the relevant partition/inheritance tree would run into a name conflict. Fyi, I also have a patch up already with that exact purpose: https://www.postgresql.org/message-id/CAOvwyF1JsmerqF6hA005rb6YLP%3DpQAPOJC= AKnr398%3D0ReZG%3DAA%40mail.gmail.com Cheers, Marko On Fri, Jun 5, 2026 at 2:56=E2=80=AFPM Chengpeng Yan wrote: > Hi, > > > On Jun 4, 2026, at 15:37, PG Bug reporting form > wrote: > > > > The following bug has been logged on the website: > > > > Bug reference: 19507 > > Logged by: Marko Grujic > > Email address: markoog@gmail.com > > PostgreSQL version: 18.4 > > Operating system: MacOS, Debian > > Description: > > > > Hi all, > > > > I've stumbled on a peculiar class of edge cases involving partitioned > > tables, where at least > > one of the partitions is in a different schema from the parent, and > > auto-named constraints. > > I believe this can be classified as a bug, and probably warrants a fix. > > > > I'm seeing this on PG18 (18.4, official Docker image): > > > > postgres=3D# select version(); > > version > > > -------------------------------------------------------------------------= ------------------------------------------------- > > PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1) on aarch64-unknown-linux-gnu, > > compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit > > (1 row) > > > > I'm also hitting it on latest master too (19beta1). > > > > The repro is quite simple, and can be reduced to 5 SQL statements: > > > > postgres=3D# create schema partitions; > > create table t(a int) partition by range (a); > > create table partitions.t_1_10 partition of t for values from (1) to > (10); > > alter table partitions.t_1_10 add constraint t_a_not_null check (a is n= ot > > null); > > alter table t alter column a set not null; > > CREATE SCHEMA > > CREATE TABLE > > CREATE TABLE > > ALTER TABLE > > ERROR: 42710: constraint "t_a_not_null" for relation "t_1_10" already > > exists > > LOCATION: AddRelationNewConstraints, heap.c:2666 > > > > Note that the last ALTER, which creates auto-named constraints, leads t= o > an > > error, because a preceding ALTER, > > which created a user-named constraint, already picked the default name > that > > the auto-generated name would use. > > > > Consequently, no new constraints are constructed for any of the tables = in > > the hierarchy. A workaround could be to > > use an explicitly-named variant of the ALTER statement, but that is not > > necessarily obvious. In addition the auto-named > > variant should really resolve any pre-existing conflicts automatically. > > > > So the exact set of circumstances to hit this bug is slightly convolute= d, > > but not unrealistic: > > 1. there's a partitioned table > > 2. which has at least one partition in another schema > > 3. that partition has a pre-existing constraint > > 4. (optional) the constraint was named by the user, and matches what PG > > would use by default > > > > Here's a couple more variants of the same issue: > > > > postgres=3D# create schema partitions; > > create table t(a int) partition by range (a); > > create table partitions.t_1_10 partition of t for values from (1) to > (10); > > alter table partitions.t_1_10 add constraint t_a_check check (a > 100); > > alter table t add check (a > 0); > > CREATE SCHEMA > > CREATE TABLE > > CREATE TABLE > > ALTER TABLE > > ERROR: 42710: constraint "t_a_check" for relation "t_1_10" already > exists > > LOCATION: MergeWithExistingConstraint, heap.c:2792 > > > > and > > > > postgres=3D# create schema partitions; > > create table t(a int) partition by range (a); > > create table partitions.t_1_10 partition of t for values from (1) to > (10); > > alter table partitions.t_1_10 add constraint t_a_not_null check (a is n= ot > > null); > > alter table t add not null a; > > CREATE SCHEMA > > CREATE TABLE > > CREATE TABLE > > ALTER TABLE > > ERROR: 42710: constraint "t_a_not_null" for relation "t_1_10" already > > exists > > LOCATION: AddRelationNewConstraints, heap.c:2666 > > > > To make matters worse, the same failure mode can be hit without the use= r > > naming the pre-existing constraint explicitly in the first place, for > > instance: > > > > postgres=3D# create schema partitions; > > create table t(a int) partition by range (a); > > create table partitions.t partition of t for values from (1) to (10); > > alter table partitions.t add check (a > 1); > > alter table t add check (a > 0); > > CREATE SCHEMA > > CREATE TABLE > > CREATE TABLE > > ALTER TABLE > > ERROR: 42710: constraint "t_a_check" for relation "t" already exists > > LOCATION: MergeWithExistingConstraint, heap.c:2792 > > Thanks for the report. I can reproduce the issue, and I agree that this > looks like a bug in the handling of automatically generated constraint > names during recursive ALTER TABLE processing. > > As your examples show, the NOT NULL and CHECK cases seem to have the > same underlying problem: the generated name is chosen for the parent > table before checking whether that candidate name will still be usable > when the constraint is propagated to partitions or inheritance children. > That can fail when a partition or child table already has a local > constraint with the same name, especially when the partition or child > table is in a different schema. > > I am working on a fix for this. My current plan is to preserve the > existing behavior for explicitly named constraints, but make > automatically generated names avoid conflicts that would be hit during > recursive propagation. In other words, when considering a generated > candidate name for the parent, the code should also consider whether > using that name throughout the relevant partition/inheritance tree would > run into a name conflict. If it would, Postgres should choose a > different generated name so that the constraints can be created > consistently throughout the tree. > > That also seems consistent with the existing distinction between > generated and explicit names. For an automatically generated name, > Postgres is responsible for picking a usable name; if the command is > going to propagate a constraint to partitions, the name should be usable > in that propagated context too. Explicitly named constraints are > different: if the user asks for a particular name and that name > conflicts, we should preserve the existing error behavior rather than > silently choosing a different name. > > The fix also needs to be careful not to treat cases where an existing > child constraint can legitimately be merged or reused as conflicts. > > I am still validating the approach and adding regression coverage. I > will post a patch once I have the details cleaned up. Comments or > suggestions are welcome. > > -- > Best regards, > Chengpeng Yan > --00000000000062635c06538142fe Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Chengpeng,

Thanks for the heads up.<= /div>

> My current plan is to preserve the
existin= g behavior for explicitly named constraints, but make
automatically gene= rated names avoid conflicts that would be hit during
recursive propagati= on. In other words, when considering a generated
candidate name for the = parent, the code should also consider whether
using that name throughout= the relevant partition/inheritance tree would
run into a name conflict.=


Cheers,
Marko

On Fri, Jun 5, 2026 at 2:56=E2=80=AF= PM Chengpeng Yan <chengpeng= _yan@outlook.com> wrote:
Hi,

> On Jun 4, 2026, at 15:37, PG Bug reporting form <noreply@postgresql.org> wr= ote:
>
> The following bug has been logged on the website:
>
> Bug reference:=C2=A0 =C2=A0 =C2=A0 19507
> Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Marko Grujic
> Email address:=C2=A0 =C2=A0 =C2=A0 markoog@gmail.com
> PostgreSQL version: 18.4
> Operating system:=C2=A0 =C2=A0MacOS, Debian
> Description:=C2=A0 =C2=A0 =C2=A0 =C2=A0
>
> Hi all,
>
> I've stumbled on a peculiar class of edge cases involving partitio= ned
> tables, where at least
> one of the partitions is in a different schema from the parent, and > auto-named constraints.
> I believe this can be classified as a bug, and probably warrants a fix= .
>
> I'm seeing this on PG18 (18.4, official Docker image):
>
> postgres=3D# select version();
>=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= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0version
> ----------------------------------------------------------------------= ----------------------------------------------------
> PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1) on aarch64-unknown-linux-gnu,=
> compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
> (1 row)
>
> I'm also hitting it on latest master too (19beta1).
>
> The repro is quite simple, and can be reduced to 5 SQL statements:
>
> postgres=3D# create schema partitions;
> create table t(a int) partition by range (a);
> create table partitions.t_1_10 partition of t for values from (1) to (= 10);
> alter table partitions.t_1_10 add constraint t_a_not_null check (a is = not
> null);
> alter table t alter column a set not null;
> CREATE SCHEMA
> CREATE TABLE
> CREATE TABLE
> ALTER TABLE
> ERROR:=C2=A0 42710: constraint "t_a_not_null" for relation &= quot;t_1_10" already
> exists
> LOCATION:=C2=A0 AddRelationNewConstraints, heap.c:2666
>
> Note that the last ALTER, which creates auto-named constraints, leads = to an
> error, because a preceding ALTER,
> which created a user-named constraint, already picked the default name= that
> the auto-generated name would use.
>
> Consequently, no new constraints are constructed for any of the tables= in
> the hierarchy. A workaround could be to
> use an explicitly-named variant of the ALTER statement, but that is no= t
> necessarily obvious. In addition the auto-named
> variant should really resolve any pre-existing conflicts automatically= .
>
> So the exact set of circumstances to hit this bug is slightly convolut= ed,
> but not unrealistic:
> 1. there's a partitioned table
> 2. which has at least one partition in another schema
> 3. that partition has a pre-existing constraint
> 4. (optional) the constraint was named by the user, and matches what P= G
> would use by default
>
> Here's a couple more variants of the same issue:
>
> postgres=3D# create schema partitions;
> create table t(a int) partition by range (a);
> create table partitions.t_1_10 partition of t for values from (1) to (= 10);
> alter table partitions.t_1_10 add constraint t_a_check check (a > 1= 00);
> alter table t add check (a > 0);
> CREATE SCHEMA
> CREATE TABLE
> CREATE TABLE
> ALTER TABLE
> ERROR:=C2=A0 42710: constraint "t_a_check" for relation &quo= t;t_1_10" already exists
> LOCATION:=C2=A0 MergeWithExistingConstraint, heap.c:2792
>
> and
>
> postgres=3D# create schema partitions;
> create table t(a int) partition by range (a);
> create table partitions.t_1_10 partition of t for values from (1) to (= 10);
> alter table partitions.t_1_10 add constraint t_a_not_null check (a is = not
> null);
> alter table t add not null a;
> CREATE SCHEMA
> CREATE TABLE
> CREATE TABLE
> ALTER TABLE
> ERROR:=C2=A0 42710: constraint "t_a_not_null" for relation &= quot;t_1_10" already
> exists
> LOCATION:=C2=A0 AddRelationNewConstraints, heap.c:2666
>
> To make matters worse, the same failure mode can be hit without the us= er
> naming the pre-existing constraint explicitly in the first place, for<= br> > instance:
>
> postgres=3D# create schema partitions;
> create table t(a int) partition by range (a);
> create table partitions.t partition of t for values from (1) to (10);<= br> > alter table partitions.t add check (a > 1);
> alter table t add check (a > 0);
> CREATE SCHEMA
> CREATE TABLE
> CREATE TABLE
> ALTER TABLE
> ERROR:=C2=A0 42710: constraint "t_a_check" for relation &quo= t;t" already exists
> LOCATION:=C2=A0 MergeWithExistingConstraint, heap.c:2792

Thanks for the report. I can reproduce the issue, and I agree that this
looks like a bug in the handling of automatically generated constraint
names during recursive ALTER TABLE processing.

As your examples show, the NOT NULL and CHECK cases seem to have the
same underlying problem: the generated name is chosen for the parent
table before checking whether that candidate name will still be usable
when the constraint is propagated to partitions or inheritance children. That can fail when a partition or child table already has a local
constraint with the same name, especially when the partition or child
table is in a different schema.

I am working on a fix for this. My current plan is to preserve the
existing behavior for explicitly named constraints, but make
automatically generated names avoid conflicts that would be hit during
recursive propagation. In other words, when considering a generated
candidate name for the parent, the code should also consider whether
using that name throughout the relevant partition/inheritance tree would run into a name conflict. If it would, Postgres should choose a
different generated name so that the constraints can be created
consistently throughout the tree.

That also seems consistent with the existing distinction between
generated and explicit names. For an automatically generated name,
Postgres is responsible for picking a usable name; if the command is
going to propagate a constraint to partitions, the name should be usable in that propagated context too. Explicitly named constraints are
different: if the user asks for a particular name and that name
conflicts, we should preserve the existing error behavior rather than
silently choosing a different name.

The fix also needs to be careful not to treat cases where an existing
child constraint can legitimately be merged or reused as conflicts.

I am still validating the approach and adding regression coverage. I
will post a patch once I have the details cleaned up. Comments or
suggestions are welcome.

--
Best regards,
Chengpeng Yan
--00000000000062635c06538142fe--