The RowExclusiveLock conflicts with queries want SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVEACCESS EXCLUSIVE locks.
In one of our customer environment we want do some DDL operation everyday through cronjobs . This cronjobs get blocked by RowExclusiveLock lock taken by UPDATE query. And then lot more queries are waiting on this cronjob as sqls under cronjob have hold ACCESS EXCLUSIVE on related tables involved in other select queries.
If we can not reduce locking in partition scenario, then it is fine. We can consider this is limitation of PostgreSQL or any other RDBMS system.
Regards,
Sachin
On Fri, Sep 16, 2016 at 7:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Sachin Kotwal <kotsachin@gmail.com> writes: > Does it release locks after taking decision and then perform actual update > operation on partition table?
No, there's no attempt to do that, and we're unlikely to consider doing so because it would result in more lock-table thrashing. Why do you care? RowExclusiveLock does not block any normal DML operation, so there's no apparent benefit from releasing it early.