pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLocking in Inheritance partition
3+ messages / 2 participants
[nested] [flat]
* Locking in Inheritance partition
@ 2021-12-10 06:35 Gaurav Anand <gaurav.anand@saama.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gaurav Anand @ 2021-12-10 06:35 UTC (permalink / raw)
To: pgsql-admin
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
[image: logo] <https://bit.ly/3Eo7O68;
--
This communication is confidential and
subject to and governed by Saama’s
Electronic
Communications Disclaimer.
<https://www.saama.com/email-communication-disclaimer/;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Locking in Inheritance partition
@ 2021-12-10 10:37 Thomas Kellerer <shammat@gmx.net>
parent: Gaurav Anand <gaurav.anand@saama.com>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Kellerer @ 2021-12-10 10:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Locking in Inheritance partition
@ 2021-12-11 16:44 Gaurav Anand <gaurav.anand@saama.com>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 3+ messages in thread
From: Gaurav Anand @ 2021-12-11 16:44 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
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
[image: logo] <https://bit.ly/3nJh91F;
--
This communication is confidential and
subject to and governed by Saama’s
Electronic
Communications Disclaimer.
<https://www.saama.com/email-communication-disclaimer/;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-12-11 16:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-10 06:35 Locking in Inheritance partition Gaurav Anand <gaurav.anand@saama.com>
2021-12-10 10:37 ` Thomas Kellerer <shammat@gmx.net>
2021-12-11 16:44 ` Gaurav Anand <gaurav.anand@saama.com>
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