Re: Why postgres take RowExclusiveLock on all partition

On Fri, Sep 16, 2016 at 09:56:39PM +0530, Sachin Kotwal wrote:
> Hi Tom,
> 
> What I understood from this https://www.postgresql.org/docs/9.5/static/
> explicit-locking.html#TABLE-LOCK-COMPATIBILITY
> is :
> 
> The RowExclusiveLock conflicts with queries want SHARE, SHARE ROW EXCLUSIVE, 
> EXCLUSIVE ACCESS 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.

We can't have DDL happening while a table is being accessed.  I guess we
could drop the lock once we are done with the partition but we don't
currently do that, and it would be complicated.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +