public inbox for [email protected]
help / color / mirror / Atom feedBUG #19507: Auto-named partition table constraint conflicts
5+ messages / 4 participants
[nested] [flat]
* BUG #19507: Auto-named partition table constraint conflicts
@ 2026-06-04 07:37 PG Bug reporting form <[email protected]>
2026-06-05 12:56 ` Re: BUG #19507: Auto-named partition table constraint conflicts Chengpeng Yan <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2026-06-04 07:37 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19507
Logged by: Marko Grujic
Email address: [email protected]
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19507: Auto-named partition table constraint conflicts
2026-06-04 07:37 BUG #19507: Auto-named partition table constraint conflicts PG Bug reporting form <[email protected]>
@ 2026-06-05 12:56 ` Chengpeng Yan <[email protected]>
2026-06-05 13:01 ` Re: BUG #19507: Auto-named partition table constraint conflicts Marko Grujic <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Chengpeng Yan @ 2026-06-05 12:56 UTC (permalink / raw)
To: [email protected] <[email protected]>; [email protected] <[email protected]>
Hi,
> On Jun 4, 2026, at 15:37, PG Bug reporting form <[email protected]> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19507
> Logged by: Marko Grujic
> Email address: [email protected]
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19507: Auto-named partition table constraint conflicts
2026-06-04 07:37 BUG #19507: Auto-named partition table constraint conflicts PG Bug reporting form <[email protected]>
2026-06-05 12:56 ` Re: BUG #19507: Auto-named partition table constraint conflicts Chengpeng Yan <[email protected]>
@ 2026-06-05 13:01 ` Marko Grujic <[email protected]>
2026-06-05 13:08 ` Re: BUG #19507: Auto-named partition table constraint conflicts Chengpeng Yan <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Marko Grujic @ 2026-06-05 13:01 UTC (permalink / raw)
To: Chengpeng Yan <[email protected]>; +Cc: [email protected] <[email protected]>
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%40ma...
Cheers,
Marko
On Fri, Jun 5, 2026 at 2:56 PM Chengpeng Yan <[email protected]>
wrote:
> Hi,
>
> > On Jun 4, 2026, at 15:37, PG Bug reporting form <[email protected]>
> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 19507
> > Logged by: Marko Grujic
> > Email address: [email protected]
> > 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19507: Auto-named partition table constraint conflicts
2026-06-04 07:37 BUG #19507: Auto-named partition table constraint conflicts PG Bug reporting form <[email protected]>
2026-06-05 12:56 ` Re: BUG #19507: Auto-named partition table constraint conflicts Chengpeng Yan <[email protected]>
2026-06-05 13:01 ` Re: BUG #19507: Auto-named partition table constraint conflicts Marko Grujic <[email protected]>
@ 2026-06-05 13:08 ` Chengpeng Yan <[email protected]>
2026-06-05 13:11 ` Re: BUG #19507: Auto-named partition table constraint conflicts Marko Grujic <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Chengpeng Yan @ 2026-06-05 13:08 UTC (permalink / raw)
To: Marko Grujic <[email protected]>; +Cc: [email protected] <[email protected]>
> On Jun 5, 2026, at 21:01, Marko Grujic <[email protected]> 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: https://www.postgresql.org/message-id/CAOvwyF1JsmerqF6hA005rb6YLP%3DpQAPOJCAKnr398%3D0ReZG%3DAA%40ma...
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19507: Auto-named partition table constraint conflicts
2026-06-04 07:37 BUG #19507: Auto-named partition table constraint conflicts PG Bug reporting form <[email protected]>
2026-06-05 12:56 ` Re: BUG #19507: Auto-named partition table constraint conflicts Chengpeng Yan <[email protected]>
2026-06-05 13:01 ` Re: BUG #19507: Auto-named partition table constraint conflicts Marko Grujic <[email protected]>
2026-06-05 13:08 ` Re: BUG #19507: Auto-named partition table constraint conflicts Chengpeng Yan <[email protected]>
@ 2026-06-05 13:11 ` Marko Grujic <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Marko Grujic @ 2026-06-05 13:11 UTC (permalink / raw)
To: Chengpeng Yan <[email protected]>; +Cc: [email protected] <[email protected]>
No worries; yeah, a review would be greatly appreciated, thank you.
On Fri, Jun 5, 2026 at 3:09 PM Chengpeng Yan <[email protected]>
wrote:
>
> > On Jun 5, 2026, at 21:01, Marko Grujic <[email protected]> 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:
> https://www.postgresql.org/message-id/CAOvwyF1JsmerqF6hA005rb6YLP%3DpQAPOJCAKnr398%3D0ReZG%3DAA%40ma...
> >
> > 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-06-05 13:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-04 07:37 BUG #19507: Auto-named partition table constraint conflicts PG Bug reporting form <[email protected]>
2026-06-05 12:56 ` Chengpeng Yan <[email protected]>
2026-06-05 13:01 ` Marko Grujic <[email protected]>
2026-06-05 13:08 ` Chengpeng Yan <[email protected]>
2026-06-05 13:11 ` Marko Grujic <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox