Re: Correct Behaviour for Concurrent partition detach

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)