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

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