Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Stop creating constraints during DETACH CONCURRENTLY

  1. BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table

    PG Bug reporting form <noreply@postgresql.org> — 2025-10-02T16:25:58Z

    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

  2. Re:BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table

    ocean_li_996 <ocean_li_996@163.com> — 2025-10-10T03:01:19Z

    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

  3. Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-10-10T11:03:08Z

    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)

  4. Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table

    ocean_li_996 <ocean_li_996@163.com> — 2025-10-10T17:14:49Z

    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

  5. Re:Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table

    songjinzhou <tsinghualucky912@foxmail.com> — 2025-10-11T02:00:54Z

    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

  6. Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahash partition table

    ocean_li_996 <ocean_li_996@163.com> — 2025-10-11T08:02:59Z

    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

  7. Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on ahashpartition table

    songjinzhou <tsinghualucky912@foxmail.com> — 2025-10-11T10:19:32Z

    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

  8. Re: Re: BUG #19070: issue with DETACH PARTITION CONCURRENTLY on a hash partition table

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-10-11T18:34:18Z

    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)