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
