Re: BUG #19507: Auto-named partition table constraint conflicts

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%3DpQAPOJCAKnr398%3D0ReZG%3DAA%40mail.gmail.com

Cheers,
Marko

On Fri, Jun 5, 2026 at 2:56 PM Chengpeng Yan <chengpeng_yan(at)outlook(dot)com>
wrote:

> Hi,
>
> > On Jun 4, 2026, at 15:37, PG Bug reporting form <noreply(at)postgresql(dot)org>
> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 19507
> > Logged by: Marko Grujic
> > Email address: markoog(at)gmail(dot)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=# 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=# 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: 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 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 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 convoluted,
> > 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=# 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=# 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: 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 user
> > naming the pre-existing constraint explicitly in the first place, for
> > instance:
> >
> > postgres=# 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
>