Обсуждение: BUG #18371: There are wrong constraint residues when detach hash partiton concurrently
BUG #18371: There are wrong constraint residues when detach hash partiton concurrently
От
PG Bug reporting form
Дата:
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?
Re:BUG #18371: There are wrong constraint residues when detach hash partiton concurrently
От
"feichanghong"
Дата:
> 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:
What I said here is wrong, the constraints on the hash partition will also take
effect. But this constraint depends on the oid of the parent partition.
> Based on the analysis above, should the added constraint for a hash partition
> be dropped after detachment?
I have initially implemented this logic in the attached patch and added a
testcase. I really hope that developers can give me some suggestions.
Best Regards,
Fei Changhong
Alibaba Cloud Computing Ltd.
Вложения
Re: BUG #18371: There are wrong constraint residues when detach hash partiton concurrently
От
Michael Paquier
Дата:
On Thu, Feb 29, 2024 at 02:21:58PM +0800, feichanghong wrote: > What I said here is wrong, the constraints on the hash partition will also take > effect. But this constraint depends on the oid of the parent partition. Something is weird with the format of the messages you have sent, by the way. > Based on the analysis above, should the added constraint for a hash partition > be dropped after detachment?I have initially implemented this logic > in the attached patch and added a testcase. I really hope that > developers can give me some suggestions. I am not much a fan of relying on ATExecDropConstraint(), where the logic introduced is a copy-paste of get_constraint_name() to retrieve the name of the constraint to drop. If any, we ought to rely more on dropconstraint_internal() instead using the pg_constraint tuple based on the OID of the constraint, and not do a cross-operation with the constraint name. But actually, why do you do a drop of the constraint at all? DetachAddConstraintIfNeeded() re-adds the constraint as it is, so instead of re-adding it as-is and then dropping it again, wouldn't it be better to just *not* re-add to begin with it if we don't need it at all? This reproduces down to 14, for what I am assuming is an issue introduced around 7b357cc6ae55. Alvaro, what do you think? -- Michael
Вложения
On 2024-Mar-05, Michael Paquier wrote: > This reproduces down to 14, for what I am assuming is an issue > introduced around 7b357cc6ae55. Alvaro, what do you think? Yeah, I have this on my list of things to fix this month. I haven't looked at it closely, but I will soon. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Los cuentos de hadas no dan al niño su primera idea sobre los monstruos. Lo que le dan es su primera idea de la posible derrota del monstruo." (G. K. Chesterton)
On 2024/3/5, 11:13, "Michael Paquier" <michael@paquier.xyz> wrote: Something is weird with the format of the messages you have sent, by the way. Ah ha! This should be a problem with my email client, I will try another client. I am not much a fan of relying on ATExecDropConstraint(), where the logic introduced is a copy-paste of get_constraint_name() to retrieve the name of the constraint to drop. If any, we ought to rely more on dropconstraint_internal() instead using the pg_constraint tuple based on the OID of the constraint, and not do a cross-operation with the constraint name. But actually, why do you do a drop of the constraint at all? DetachAddConstraintIfNeeded() re-adds the constraint as it is, so instead of re-adding it as-is and then dropping it again, wouldn't it be better to just *not* re-add to begin with it if we don't need it at all? After verification, the partition constraints on the partition being detached remain in effect until the second transaction committed. This newly added constraint should only be for the purpose of speeding up the re-attach operation. Perhaps I've missed something? So, for hash partitioning, it should be better not to re-add new constraints. Perhaps we should filter out hash partitions in DetachAddConstraintIfNeeded(). -- Best Regards, Fei Changhong Alibaba Cloud Computing Ltd.
Re: BUG #18371: There are wrong constraint residues when detach hash partiton concurrently
От
Michael Paquier
Дата:
On Tue, Mar 05, 2024 at 08:37:27AM +0100, Alvaro Herrera wrote: > Yeah, I have this on my list of things to fix this month. I haven't > looked at it closely, but I will soon. Cool, thanks! -- Michael