public inbox for [email protected]  

help / color / mirror / Atom feed

From: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19507: Auto-named partition table constraint conflicts
Date: Thu, 04 Jun 2026 07:37:13 +0000
Message-ID: <[email protected]> (raw)

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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: BUG #19507: Auto-named partition table constraint conflicts
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox