BUG #19507: Auto-named partition table constraint conflicts

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