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
