Обсуждение: Locking in Inheritance partition
Hi Team,
We have a declarative partition on tables in our database which is PG10. We face issues as the table becomes locked during attach and detach and cannot be used during the event.
We have a master and slave setup and application query points to Slave but whenever we perform attach and detach of partitions on Master the locks propagates to slave and causes Application to spin.
Kindly suggest if Inheritance partition will help here or any other way I can avoid this lock.
Thanks,
Gaurav
-- Thanks.
Regards,
Gaurav Anand
This communication is confidential and subject to and governed by Saama’s Electronic Communications Disclaimer.
Thank you Thomas.
Upgrade is not planned in near future.
With pg10, we added advisory lock. but when it comes to detach attach we are still struggling.
On Fri, 10 Dec 2021 at 4:07 PM, Thomas Kellerer <shammat@gmx.net> wrote:
Gaurav Anand schrieb am 10.12.2021 um 07:35:
> We have a declarative partition on tables in our database which is
> PG10. We face issues as the table becomes locked during attach and
> detach and cannot be used during the event.
>
> We have a master and slave setup and application query points to
> Slave but whenever we perform attach and detach of partitions on
> Master the locks propagates to slave and causes Application to spin.
>
> Kindly suggest if Inheritance partition will help here or any other
> way I can avoid this lock.
I don't think inheritance would change the locks this requires.
A better solution might be to upgrade to 14 where both attach
and detach are non-blocking.
Postgres 12 introduced non-blocking attach and Postgres 14
introduced non-blocking detach
Thomas
Thanks.
Regards,
Gaurav Anand
This communication is confidential and subject to and governed by Saama’s Electronic Communications Disclaimer.