Re: Why postgres take RowExclusiveLock on all partition

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.

Regards,
Sachin

On Fri, Sep 16, 2016 at 7:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Sachin Kotwal <kotsachin(at)gmail(dot)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.
>
> regards, tom lane
>

--

Thanks and Regards,
Sachin Kotwal