Re: Postgres 10 - Attach-Detach Partition in Declarative vs Inheritance

On Mon, 2022-02-21 at 19:34 +0530, Gaurav Anand wrote:
> I need your kind recommendation
> We have Postgres 10 and have partition tables currently with Declarative partitions.
> When we refresh the data and detach and attach the partition, during this time our
> Application which has Sql query on these tables takes Lock and sometimes a deadlock situation also arises.
>  
> To overcome the deadlock we used, advisory lock however the application still keeps waiting for lock to release.
>
> Please suggest the best way - 
> 1. Will Inheritance partition (in PG10) help avoid the explicit locks.
> 2. Is upgrading the only choice? if so which version of PG.
> 3. Please suggest any other work around.

You are confused: "inheritance partitioning" is the bad old way of doing it
that was your only option before v10. You don't want that.

But there have been substantial improvements since v10:

- ALTER TABLE ... ATTACH PARTITION now only takes a SHARE UPDATE EXCLUSIVE
lock on the partitioned table (that used to be ACCESS EXCLUSIVE)

- there is ALTER TABLE ... DETACH PARTITION CONCURRENTLY that requires
only a SHARE UPDATE EXCLUSIVE lock on the partitioned table

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com