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

Hi Team,

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.

Thanks.

Regards,

Gaurav Anand

This communication is confidential and subject to and governed by Saama’s Electronic Communications Disclaimer.

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