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.
What do you think?
—
Regards
Haiyang Li