Subject: Re: Locking in Inheritance partition
To: pgsql-admin@lists.postgresql.org
References: 
 <CAMZ99nd5AxvTi9ydjv-FJWmaWbo-npoqLciOmyOPhm7BERjEPQ@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <396391e4-e36a-d51d-2ef1-1e454fd4d6fa@gmx.net>
Date: Fri, 10 Dec 2021 11:37:42 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: 
 <CAMZ99nd5AxvTi9ydjv-FJWmaWbo-npoqLciOmyOPhm7BERjEPQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/396391e4-e36a-d51d-2ef1-1e454fd4d6fa%40gmx.net>
Precedence: bulk

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