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:
hereBut the Code says it will wait for all transaction that could've seen that partition will be waited for:
here Which is True.
In this
line, 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
