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