Thread

1. Correct Behaviour for Concurrent partition detach

   Rohit Negi <rohit25.negi@gmail.com> — 2025-07-17T09:25:32Z

   Hi Postgres Team,
   
   This is Rohit, Sending out this email to question the right behaviour of
   the partition detaching.
   
   *Context*:
   We have started using the partitions in postgres and during the partition
   management process when we try to detach the partitions the detach command
   seems to be stuck for >50minutes.
   
   Upon checking we found that, It is waiting for a sharedLock on a
   virtualxid. This virtualxid belongs to another query which is a `fetch
   foward` cursor query for a select query.
   
   The partition we are trying to detach is different from the partition this
   select query is supposed to look for. The select query is fired on the
   parent table, But with the partition key filter which should point it to
   the correct partition.
   
   *Query we are running for detaching:*
   *ALTER TABLE <TABLE_NAME> DETACH PARTITION CONCURRENTLY.*
   
   Number of rows in the partition = 0.
   
   
   *Question*:
   Is this expected that any query(including select queries) running on the
   parent table will block the DETACH partition query ? Is this also expected
   if the running select query was not looking into the partition being
   detached?
   
   *Inconsistency or clarification needed:*
   Postgres doc says it will wait for all transactions on the parent table:
   here
   <https://www.postgresql.org/docs/16/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION:~:text=all%20other%20transactions%20using%20the%20partitioned%20table%20are%20waited%20for.>
   But the Code says it will wait for all transaction that could've seen that
   partition will be waited for: here
   <https://github.com/postgres/postgres/blob/master/src/backend/commands/tablecmds.c#L20857>
   
   Which is True.
   In this line
   <https://github.com/postgres/postgres/blob/master/src/backend/commands/tablecmds.c#L20986>,
   Should it be passing the `*partrelid*`  instead of `*parentrelid*` to only
   wait for transactions involving the partition table?
   Currently, I am suspecting that it's waiting for any query running on the
   parent table regarding which partition that query points to.
   
   
   
   *What we Tried:*
   1. We ran a nested query on the parent table with the cursor. This query
   points to the partition X based on the filter supplied.
   2. We ran the DETACH PARTITION CONCURRENTLY to Detach the partition Y.
   3. (2) seems to wait on 1's virtualxid. Why is this?
   
   
   SS Attached:
   PID = 12381 (DETACH Query)
   PID = 13029 (CURSOR fetch forward for the select query, running on a
   different partition)
   
   
   Thanks, We are kind of blocked on this to identify the RC. It would be
   great if we can get some guidance here.
   
   
   Thanks.
   Rohit,
   Staff Engineer @ Moveworks
   

2. Re: Correct Behaviour for Concurrent partition detach

   Álvaro Herrera <alvherre@kurilemu.de> — 2025-07-17T13:20:30Z

   On 2025-Jul-17, Rohit Negi wrote:
   
   > *Question*:
   > Is this expected that any query(including select queries) running on
   > the parent table will block the DETACH partition query ? Is this also
   > expected if the running select query was not looking into the
   > partition being detached?
   
   Yeah, the DETACH needs to wait until those snapshots are gone, as you
   saw in the code.
   
   > *Inconsistency or clarification needed:*
   > Postgres doc says it will wait for all transactions on the parent table:
   > here
   > <https://www.postgresql.org/docs/16/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION:~:text=all%20other%20transactions%20using%20the%20partitioned%20table%20are%20waited%20for.>
   > But the Code says it will wait for all transaction that could've seen that
   > partition will be waited for: here
   > <https://github.com/postgres/postgres/blob/master/src/backend/commands/tablecmds.c#L20857>
   
   I read these both as saying the same thing.  The "partitioned table" is
   the same as the "parent table".
   
   > In this line
   > <https://github.com/postgres/postgres/blob/master/src/backend/commands/tablecmds.c#L20986>,
   > Should it be passing the `*partrelid*`  instead of `*parentrelid*` to only
   > wait for transactions involving the partition table?
   
   No, because what we want is to be able to rebuild a partition
   descriptor for the parent table which no longer contains the partition
   that's being detached.
   
   > Currently, I am suspecting that it's waiting for any query running on the
   > parent table regarding which partition that query points to.
   
   That sounds reasonable, yeah.
   
   I think the solution for your problem is along the lines of closing the
   cursor within a reasonable timeframe.  Holding a query open for 40
   minutes doesn't sound reasonable.  If you need that, then you need to be
   able to cope with detach lasting that long.
   
   -- 
   Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
   "Java is clearly an example of money oriented programming"  (A. Stepanov)