Feedback-ID: ie3de48e3:Fastmail
Date: Thu, 17 Jul 2025 15:20:30 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Rohit Negi <rohit25.negi@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Correct Behaviour for Concurrent partition detach
Message-ID: <202507171320.qcknqc6zoci2@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAGRR6qrUa64xrwsCch0kyS5kRS=n1a1d4bQKk5Znrf3j_Os_ag@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202507171320.qcknqc6zoci2%40alvherre.pgsql>
Precedence: bulk

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)