pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table
8+ messages / 4 participants
[nested] [flat]
* BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table
@ 2025-10-02 16:25 PG Bug reporting form <noreply@postgresql.org>
2025-10-10 03:01 ` Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
0 siblings, 1 reply; 8+ messages in thread
From: PG Bug reporting form @ 2025-10-02 16:25 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: mohen.lhy@alibaba-inc.com
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table
2025-10-02 16:25 BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table PG Bug reporting form <noreply@postgresql.org>
@ 2025-10-10 03:01 ` ocean_li_996 <ocean_li_996@163.com>
2025-10-10 11:03 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 1 reply; 8+ messages in thread
From: ocean_li_996 @ 2025-10-10 03:01 UTC (permalink / raw)
To: mohen.lhy@alibaba-inc.com <mohen.lhy@alibaba-inc.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; alvherre@kurilemu.de; robertmhaas@gmail.com
Hi all,
Since there has been no update in this thread for a few days, I reached out to the primary committer for this functionality.
No offense meant; I’m simply interested in getting your perspective.
--
Regards
Haiyang Li
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table
2025-10-02 16:25 BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table PG Bug reporting form <noreply@postgresql.org>
2025-10-10 03:01 ` Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
@ 2025-10-10 11:03 ` Álvaro Herrera <alvherre@kurilemu.de>
2025-10-10 17:14 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
0 siblings, 1 reply; 8+ messages in thread
From: Álvaro Herrera @ 2025-10-10 11:03 UTC (permalink / raw)
To: ocean_li_996 <ocean_li_996@163.com>; +Cc: mohen.lhy@alibaba-inc.com <mohen.lhy@alibaba-inc.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; robertmhaas@gmail.com
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table
2025-10-02 16:25 BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table PG Bug reporting form <noreply@postgresql.org>
2025-10-10 03:01 ` Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
2025-10-10 11:03 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table Álvaro Herrera <alvherre@kurilemu.de>
@ 2025-10-10 17:14 ` ocean_li_996 <ocean_li_996@163.com>
2025-10-11 02:00 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table =?utf-8?B?c29uZ2ppbnpob3U=?= <tsinghualucky912@foxmail.com>
2025-10-11 18:34 ` Re: Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 2 replies; 8+ messages in thread
From: ocean_li_996 @ 2025-10-10 17:14 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@kurilemu.de>; +Cc: mohen.lhy@alibaba-inc.com <mohen.lhy@alibaba-inc.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; robertmhaas@gmail.com
Hi, Álvaro
At 2025-10-10 19:03:08, "Álvaro Herrera" <alvherre@kurilemu.de> wrote:
>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).
Thanks for your feedback. It seems I might have overlooked something.
At first, I assumed this was expected behavior, and after searching
the pgsql-hackers archives I did not find any related threads. I also
realize I haven’t checked pgsql-bugs.
>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.
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.
--
regards
Haiyang Li
Attachments:
[application/octet-stream] 0001_master_fix_detach_partition_concurrently_issue.patch (5.5K, ../../17a8f848.b7f6.199cf1e3b5e.Coremail.ocean_li_996@163.com/3-0001_master_fix_detach_partition_concurrently_issue.patch)
download | inline diff:
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b3ed69457fc..f78fca03182 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -720,7 +720,6 @@ static void QueuePartitionConstraintValidation(List **wqueue, Relation scanrel,
List *partConstraint,
bool validate_default);
static void CloneRowTriggersToPartition(Relation parent, Relation partition);
-static void DetachAddConstraintIfNeeded(List **wqueue, Relation partRel);
static void DropClonedTriggersFromPartition(Oid partitionId);
static ObjectAddress ATExecDetachPartition(List **wqueue, AlteredTableInfo *tab,
Relation rel, RangeVar *name,
@@ -20869,12 +20868,6 @@ ATExecDetachPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
char *parentrelname;
char *partrelname;
- /*
- * Add a new constraint to the partition being detached, which
- * supplants the partition constraint (unless there is one already).
- */
- DetachAddConstraintIfNeeded(wqueue, partRel);
-
/*
* We're almost done now; the only traces that remain are the
* pg_inherits tuple and the partition's relpartbounds. Before we can
@@ -21323,49 +21316,6 @@ ATExecDetachPartitionFinalize(Relation rel, RangeVar *name)
return address;
}
-/*
- * DetachAddConstraintIfNeeded
- * Subroutine for ATExecDetachPartition. Create a constraint that
- * takes the place of the partition constraint, but avoid creating
- * a dupe if a constraint already exists which implies the needed
- * constraint.
- */
-static void
-DetachAddConstraintIfNeeded(List **wqueue, Relation partRel)
-{
- List *constraintExpr;
-
- constraintExpr = RelationGetPartitionQual(partRel);
- constraintExpr = (List *) eval_const_expressions(NULL, (Node *) constraintExpr);
-
- /*
- * Avoid adding a new constraint if the needed constraint is implied by an
- * existing constraint
- */
- if (!PartConstraintImpliedByRelConstraint(partRel, constraintExpr))
- {
- AlteredTableInfo *tab;
- Constraint *n;
-
- tab = ATGetQueueEntry(wqueue, partRel);
-
- /* Add constraint on partition, equivalent to the partition constraint */
- n = makeNode(Constraint);
- n->contype = CONSTR_CHECK;
- n->conname = NULL;
- n->location = -1;
- n->is_no_inherit = false;
- n->raw_expr = NULL;
- n->cooked_expr = nodeToString(make_ands_explicit(constraintExpr));
- n->is_enforced = true;
- n->initially_valid = true;
- n->skip_validation = true;
- /* It's a re-add, since it nominally already exists */
- ATAddCheckNNConstraint(wqueue, tab, partRel, n,
- true, false, true, ShareUpdateExclusiveLock);
- }
-}
-
/*
* DropClonedTriggersFromPartition
* subroutine for ATExecDetachPartition to remove any triggers that were
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 476266e3f4b..e2bffc7be21 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -4472,8 +4472,6 @@ Number of partitions: 0
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
-Check constraints:
- "part_rp_a_check" CHECK (a IS NOT NULL AND a >= 0 AND a < 100)
CREATE TABLE part_rp100 PARTITION OF range_parted2 (CHECK (a>=123 AND a<133 AND a IS NOT NULL)) FOR VALUES FROM (100) to (200);
ALTER TABLE range_parted2 DETACH PARTITION part_rp100 CONCURRENTLY;
@@ -4487,6 +4485,30 @@ Check constraints:
"part_rp100_a_check" CHECK (a >= 123 AND a < 133 AND a IS NOT NULL)
DROP TABLE range_parted2;
+CREATE TABLE hash_parted2 (
+ a int
+) PARTITION BY HASH(a);
+CREATE TABLE part_hp PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
+ALTER TABLE hash_parted2 DETACH PARTITION part_hp CONCURRENTLY;
+\d+ hash_parted2
+ Partitioned table "public.hash_parted2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: HASH (a)
+Number of partitions: 0
+
+-- constraint should not be created
+\d part_hp
+ Table "public.part_hp"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+
+DROP TABLE hash_parted2;
+-- work fine
+INSERT INTO part_hp VALUES (1);
+DROP TABLE part_hp;
-- Check ALTER TABLE commands for partitioned tables and partitions
-- cannot add/drop column to/from *only* the parent
ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 5ce9d1e429f..06d4a8be60f 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2832,6 +2832,19 @@ ALTER TABLE range_parted2 DETACH PARTITION part_rp100 CONCURRENTLY;
\d part_rp100
DROP TABLE range_parted2;
+CREATE TABLE hash_parted2 (
+ a int
+) PARTITION BY HASH(a);
+CREATE TABLE part_hp PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
+ALTER TABLE hash_parted2 DETACH PARTITION part_hp CONCURRENTLY;
+\d+ hash_parted2
+-- constraint should not be created
+\d part_hp
+DROP TABLE hash_parted2;
+-- work fine
+INSERT INTO part_hp VALUES (1);
+DROP TABLE part_hp;
+
-- Check ALTER TABLE commands for partitioned tables and partitions
-- cannot add/drop column to/from *only* the parent
[application/octet-stream] 0001_stable_fix_detach_partition_concurrently_issue.patch (4.0K, ../../17a8f848.b7f6.199cf1e3b5e.Coremail.ocean_li_996@163.com/4-0001_stable_fix_detach_partition_concurrently_issue.patch)
download | inline diff:
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b3ed69457fc..7612e9a3f86 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20797,13 +20797,14 @@ ATExecDetachPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
Relation partRel;
ObjectAddress address;
Oid defaultPartOid;
+ PartitionDesc partdesc;
/*
* We must lock the default partition, because detaching this partition
* will change its partition constraint.
*/
- defaultPartOid =
- get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true));
+ partdesc = RelationGetPartitionDesc(rel, true);
+ defaultPartOid = get_default_oid_from_partdesc(partdesc);
if (OidIsValid(defaultPartOid))
{
/*
@@ -20872,8 +20873,19 @@ ATExecDetachPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/*
* Add a new constraint to the partition being detached, which
* supplants the partition constraint (unless there is one already).
+ *
+ * In stable branches, we currently skip adding this constraint
+ * for hash partitions. The reason is to avoid leaving behind
+ * OID-dependent check constraints referencing the parent table's OID,
+ * which could lead to unexpected errors(e.g. Could not open relation
+ * with OID xxxx).
+ *
+ * For other partitioning strategies, we keep the previous behavior
+ * to prevent breaking existing workloads that might rely on the
+ * absence of such constraints.
*/
- DetachAddConstraintIfNeeded(wqueue, partRel);
+ if (partdesc->boundinfo->strategy != PARTITION_STRATEGY_HASH)
+ DetachAddConstraintIfNeeded(wqueue, partRel);
/*
* We're almost done now; the only traces that remain are the
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 476266e3f4b..fd5b1b35bda 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -4487,6 +4487,30 @@ Check constraints:
"part_rp100_a_check" CHECK (a >= 123 AND a < 133 AND a IS NOT NULL)
DROP TABLE range_parted2;
+CREATE TABLE hash_parted2 (
+ a int
+) PARTITION BY HASH(a);
+CREATE TABLE part_hp PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
+ALTER TABLE hash_parted2 DETACH PARTITION part_hp CONCURRENTLY;
+\d+ hash_parted2
+ Partitioned table "public.hash_parted2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: HASH (a)
+Number of partitions: 0
+
+-- constraint should not be created
+\d part_hp
+ Table "public.part_hp"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+
+DROP TABLE hash_parted2;
+-- work fine
+INSERT INTO part_hp VALUES (1);
+DROP TABLE part_hp;
-- Check ALTER TABLE commands for partitioned tables and partitions
-- cannot add/drop column to/from *only* the parent
ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 5ce9d1e429f..06d4a8be60f 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2832,6 +2832,19 @@ ALTER TABLE range_parted2 DETACH PARTITION part_rp100 CONCURRENTLY;
\d part_rp100
DROP TABLE range_parted2;
+CREATE TABLE hash_parted2 (
+ a int
+) PARTITION BY HASH(a);
+CREATE TABLE part_hp PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
+ALTER TABLE hash_parted2 DETACH PARTITION part_hp CONCURRENTLY;
+\d+ hash_parted2
+-- constraint should not be created
+\d part_hp
+DROP TABLE hash_parted2;
+-- work fine
+INSERT INTO part_hp VALUES (1);
+DROP TABLE part_hp;
+
-- Check ALTER TABLE commands for partitioned tables and partitions
-- cannot add/drop column to/from *only* the parent
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table
2025-10-02 16:25 BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table PG Bug reporting form <noreply@postgresql.org>
2025-10-10 03:01 ` Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
2025-10-10 11:03 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table Álvaro Herrera <alvherre@kurilemu.de>
2025-10-10 17:14 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
@ 2025-10-11 02:00 ` =?utf-8?B?c29uZ2ppbnpob3U=?= <tsinghualucky912@foxmail.com>
2025-10-11 08:02 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table ocean_li_996 <ocean_li_996@163.com>
1 sibling, 1 reply; 8+ messages in thread
From: songjinzhou @ 2025-10-11 02:00 UTC (permalink / raw)
To: ocean_li_996 <ocean_li_996@163.com>; Álvaro Herrera <alvherre@kurilemu.de>; +Cc: mohen.lhy@alibaba-inc.com <mohen.lhy@alibaba-inc.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; robertmhaas <robertmhaas@gmail.com>
Hello hackers, Haiyang. I think the direct deletion of the DetachAddConstraintIfNeeded function in the patch above might not be a good idea. Will this create new problems?
Of the two solutions Haiyang mentioned earlier, is the second one more appropriate? Should it be deleted after detaching? For example, somewhere in the DetachPartitionFinalize function?
Of course, these are just my personal thoughts. Thank you.
songjinzhou
tsinghualucky912@foxmail.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table
2025-10-02 16:25 BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table PG Bug reporting form <noreply@postgresql.org>
2025-10-10 03:01 ` Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
2025-10-10 11:03 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table Álvaro Herrera <alvherre@kurilemu.de>
2025-10-10 17:14 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
2025-10-11 02:00 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table =?utf-8?B?c29uZ2ppbnpob3U=?= <tsinghualucky912@foxmail.com>
@ 2025-10-11 08:02 ` ocean_li_996 <ocean_li_996@163.com>
2025-10-11 10:19 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahashpartition table =?utf-8?B?c29uZ2ppbnpob3U=?= <tsinghualucky912@foxmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: ocean_li_996 @ 2025-10-11 08:02 UTC (permalink / raw)
To: songjinzhou <tsinghualucky912@foxmail.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; mohen.lhy@alibaba-inc.com <mohen.lhy@alibaba-inc.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; robertmhaas <robertmhaas@gmail.com>
Hello jinzhou,
Thanks for your opinion.
At 2025-10-11 10:00:54, "songjinzhou" <tsinghualucky912@foxmail.com> wrote:
> Hello hackers, Haiyang. I think the direct deletion of the DetachAddConstraintIfNeeded function in the patch above might not be a
> good idea. Will this create new problems?
>
> Of the two solutions Haiyang mentioned earlier, is the second one more appropriate? Should it be deleted after detaching? For
> example, somewhere in the DetachPartitionFinalize function?
AFAICS, the mainly difference between "Direct deletion of the DetachAddConstraintIfNeeded function" (thought 1)
and "Adding constraint first and removing it later" (thought 2) is the moment after the first transaction commit
of detach concurrently. In that moment, thought 1 will not add a constraint on detached partition and thought 2 will.
However, detached partition still have a partition constraint, which is functionally redundant with the newly added
constraint for a partition table. Regarding the partitioned table, once first transaction has committed, the partition
can be regarded as already detached from its parent. Therefore, I’m inclined to go with thought 1.
--
regards
Haiyang Li
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahashpartition table
2025-10-02 16:25 BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table PG Bug reporting form <noreply@postgresql.org>
2025-10-10 03:01 ` Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
2025-10-10 11:03 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table Álvaro Herrera <alvherre@kurilemu.de>
2025-10-10 17:14 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
2025-10-11 02:00 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table =?utf-8?B?c29uZ2ppbnpob3U=?= <tsinghualucky912@foxmail.com>
2025-10-11 08:02 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table ocean_li_996 <ocean_li_996@163.com>
@ 2025-10-11 10:19 ` =?utf-8?B?c29uZ2ppbnpob3U=?= <tsinghualucky912@foxmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: songjinzhou @ 2025-10-11 10:19 UTC (permalink / raw)
To: ocean_li_996 <ocean_li_996@163.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; mohen.lhy@alibaba-inc.com <mohen.lhy@alibaba-inc.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; robertmhaas <robertmhaas@gmail.com>
Hi Haiyang, thank you for your explanation. I just verified your patch and it works fine. This should be a redundancy. I have no problem with it now. Thank you.
songjinzhou
tsinghualucky912@foxmail.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table
2025-10-02 16:25 BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table PG Bug reporting form <noreply@postgresql.org>
2025-10-10 03:01 ` Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
2025-10-10 11:03 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table Álvaro Herrera <alvherre@kurilemu.de>
2025-10-10 17:14 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table ocean_li_996 <ocean_li_996@163.com>
@ 2025-10-11 18:34 ` Álvaro Herrera <alvherre@kurilemu.de>
1 sibling, 0 replies; 8+ messages in thread
From: Álvaro Herrera @ 2025-10-11 18:34 UTC (permalink / raw)
To: ocean_li_996 <ocean_li_996@163.com>; +Cc: mohen.lhy@alibaba-inc.com <mohen.lhy@alibaba-inc.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-10-11 18:34 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-02 16:25 BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table PG Bug reporting form <noreply@postgresql.org>
2025-10-10 03:01 ` ocean_li_996 <ocean_li_996@163.com>
2025-10-10 11:03 ` Álvaro Herrera <alvherre@kurilemu.de>
2025-10-10 17:14 ` ocean_li_996 <ocean_li_996@163.com>
2025-10-11 02:00 ` Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table =?utf-8?B?c29uZ2ppbnpob3U=?= <tsinghualucky912@foxmail.com>
2025-10-11 08:02 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table ocean_li_996 <ocean_li_996@163.com>
2025-10-11 10:19 ` Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahashpartition table =?utf-8?B?c29uZ2ppbnpob3U=?= <tsinghualucky912@foxmail.com>
2025-10-11 18:34 ` Álvaro Herrera <alvherre@kurilemu.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox