From: Álvaro 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
Date: Thu, 17 Jul 2025 15:20:30 +0200
Message-ID: <202507171320.qcknqc6zoci2@alvherre.pgsql> (raw)
In-Reply-To: <CAGRR6qrUa64xrwsCch0kyS5kRS=n1a1d4bQKk5Znrf3j_Os_ag@mail.gmail.com>
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%20...;
> 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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: alvherre@kurilemu.de, rohit25.negi@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Correct Behaviour for Concurrent partition detach
In-Reply-To: <202507171320.qcknqc6zoci2@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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