Обсуждение: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table

The following bug has been logged on the website:

Bug reference:      19070
Logged by:          haiyang li
Email address:      mohen.lhy@alibaba-inc.com
PostgreSQL version: 18.0
Operating system:   any
Description:

Hi all,
DETACH PARTITION CONCURRENTLY will add a check constraint to detached
partition.
However, I found an issue when using it on a hash partitioned table.

The repro steps are as follows:
```
CREATE DATABASE db1;
CREATE DATABASE db2;
\c db1
CREATE TABLE users (id int, name text) PARTITION BY HASH (id);
CREATE TABLE users_p0 PARTITION OF users
 FOR VALUES WITH (MODULUS 4, REMAINDER 0);s
CREATE TABLE users_p1 PARTITION OF users
 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users
 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users
 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
insert into users values (1, 'a'),(2, 'b'),(3, 'c'),(4, 'd’);

ALTER TABLE users detach partition users_p0 concurrently ;

\d+ users_p0
 Table "public.users_p0"
Column | Type | Collation | Nullable | Default | Storage | Compression |
Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
name | text | | | | extended | | |
Check constraints:
 "users_p0_id_check" CHECK (satisfies_hash_partition('16990'::oid, 4, 0,
id))
Access method: heap

\q

pg_dump -Fc db1 -U postgres -t users -t users_p0 > users.dump
pg_restore users.dump -d db2 -U postgres
```

The restore fails with:
```
pg_restore: error: COPY failed for table "users_p0": ERROR: could not open
relation with OID 16990
CONTEXT: COPY users_p0, line 1: "1 a"
pg_restore: warning: errors ignored on restore: 1
```

After detach, users_p0 has an added check constraint users_p0_id_check
calling satisfies_hash_partition.
The first parameter is the OID of users. In the dump, the CREATE TABLE for
users_p0 is:
```
CREATE TABLE public.users_p0 (
 id integer,
 name text,
 CONSTRAINT users_p0_id_check CHECK (satisfies_hash_partition('16990'::oid,
4, 0, id))
);
```

When restoring to db2, any insert into users_p0 fails with ERROR: could not
open relation with OID xxxx.
This behavior seems undesirable. OHOH, a detached partition should become a
plain table.
The fact that detach adds an extra constraint feels odd, i think.

I can think of two possible solutions:
1) Change satisfies_hash_partition(oid,int4,int4,any) to
satisfies_hash_partition(text,text,int4,int4,any),
with the first two params being schema name and table name. This could only
be applied to the master
branch, and is not perfect — e.g., a RENAME on the parent table would cause
other errors.

2) Drop the added constraint after detach. This sounds cleaner, but would
require storing additional
metadata in the catalog to identify such constraints, so we can remove them
even after recovery.
Again, this is probably only for the master branch.

any thought?

—
Regards
Haiyang Li

On 2025-Oct-10, ocean_li_996 wrote:

> Since there has been no update in this thread for a few days, I
> reached out to the primary committer for this functionality.

Yes, this is buggy, and has been reported previously (#18371).  I'll fix
it before the November minors, probably by removing the step of creating
the constraints during detach.  I think for stable branches I'll do that
only for hash partitioning, to avoid breaking workloads that might
depend on those constraints, but for branch master I'll do it for all
partitioning strategies.

> No offense meant; I’m simply interested in getting your perspective.

Yeah, I should have reacted sooner to the previous report of this bug.
No offense taken.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)

On 2025-Oct-11, ocean_li_996 wrote:

> Based on the discussion in #18371 and my own investigation, I think the
> proposed fix is appropriate. I’m attaching patches for both the stable
> and master branches, FYI.

Yeah, looks good -- pushed to all branches since 14.  One thing I
modified in branch master is that the test just above the new one is
testing that a constraint is being added, but that's no longer true, so
I removed some lines there.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)