BUG #19507: Auto-named partition table constraint conflicts

Hi,

On Jun 4, 2026, at 15:37, PG Bug reporting form <noreply@postgresql.org> 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=# 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

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:
/messages/by-id/CAOvwyF1JsmerqF6hA005rb6YLP=pQAPOJCAKnr398=0ReZG=AA@mail.gmail.com

Cheers,
Marko

On Fri, Jun 5, 2026 at 2:56 PM Chengpeng Yan <chengpeng_yan@outlook.com>
wrote:

On Jun 5, 2026, at 21:01, Marko Grujic <markoog@gmail.com> wrote:

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: /messages/by-id/CAOvwyF1JsmerqF6hA005rb6YLP=pQAPOJCAKnr398=0ReZG=AA@mail.gmail.com

Cheers,
Marko

Sorry, I hadn't noticed that you had already posted a patch for this.

Thanks for pointing it out. I'll take a look and try to review it when I
get some time.

--
Best regards,
Chengpeng Yan

No worries; yeah, a review would be greatly appreciated, thank you.

On Fri, Jun 5, 2026 at 3:09 PM Chengpeng Yan <chengpeng_yan@outlook.com>
wrote: