BUG #18371: There are wrong constraint residues when detach hash partiton concurrently
PG Bug reporting form <noreply@postgresql.org>
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: feichanghong@qq.com
Date: 2024-02-28T11:30:46Z
Lists: pgsql-bugs
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Stop creating constraints during DETACH CONCURRENTLY
- ea06f97eeb33 17.7 landed
- bdae98495660 15.15 landed
- b835759ec7e8 16.11 landed
- b753be38a4a2 14.20 landed
- 3231fd045522 19 (unreleased) landed
- 08c037dff961 18.1 landed
-
Rename SLRU elements in view pg_stat_slru
- bcdfa5f2e2f2 17.0 cited
The following bug has been logged on the website:
Bug reference: 18371
Logged by: Fei Changhong
Email address: feichanghong@qq.com
PostgreSQL version: 16.2
Operating system: Operating system: centos 7,Kernel version: 5.10.13
Description:
Hi all,
When I tried to insert data into a table that had just been detached, I
encountered an error: "ERROR: could not open relation with OID 16384".
The environment information is as follows:
PostgreSQL branch: master.
commit: bcdfa5f2e2f274caeed20b2f986012a9cb6a259c
This issue can be reproduced with the following steps:
```
create table t(a int) partition by hash (a);
create table t_p1 partition of t for values with (modulus 2, remainder 0);
alter table t detach partition t_p1 concurrently ;
drop table t;
insert into t_p1 select 1;
```
When detaching a partition concurrently, the function
DetachAddConstraintIfNeeded
is called to convert the partition constraint of the detached partition into
a
regular constraint, and it is not dropped at the end of the detach
process.
This can work normally on range partitions. However, the constraint on
hash
partitions uses satisfies_hash_partition with the OID of the parent table,
and
the newly created constraint does not take effect. For example, in the
following
case, although there is a t_p1_a_check constraint on t_p1, it is still
possible
to perform an insert:
```
create table t(a int) partition by hash (a);
create table t_p1 partition of t for values with (modulus 2, remainder 0);
alter table t detach partition t_p1 concurrently ;
postgres=# \d+ t_p1
Table "public.t_p1"
Column | Type | Collation | Nullable | Default | Storage | Compression |
Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | |
|
Check constraints:
"t_p1_a_check" CHECK (satisfies_hash_partition('16384'::oid, 2, 0, a))
Access method: heap
postgres=# insert into t_p1 select 1;
INSERT 0 1
postgres=#
```
If the parent table has already been dropped, an error will occur during
constraint checking.
Based on the analysis above, should the added constraint for a hash
partition
be dropped after detachment?