Re: Why postgres take RowExclusiveLock on all partition
| От | Sachin Kotwal |
|---|---|
| Тема | Re: Why postgres take RowExclusiveLock on all partition |
| Дата | |
| Msg-id | CA+N_YAfB9qnbn+mj78KKgLwO_hWFE+tMEJgz27BJSP-PKZqXgw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Why postgres take RowExclusiveLock on all partition (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Why postgres take RowExclusiveLock on all partition
|
| Список | pgsql-hackers |
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@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.
regards, tom lane
Sachin Kotwal
В списке pgsql-hackers по дате отправления: