Thread: Why postgres take RowExclusiveLock on all partition

Why postgres take RowExclusiveLock on all partition

From
Sachin Kotwal
Date:
Hi Hackers,


I checked if there  is update transaction on master table involved in partition.
Postgresql takes  RowExclusiveLock on all partition tables.

constraint exclusion is set to on.

My question is  why it locks on all partition tables instead only one partition tables where data is resides?


Feel free to ask if any further information is required .


--

Thanks and Regards,
Sachin Kotwal

Re: Why postgres take RowExclusiveLock on all partition

From
Ashutosh Bapat
Date:
On Fri, Sep 16, 2016 at 4:31 PM, Sachin Kotwal <kotsachin@gmail.com> wrote:
> Hi Hackers,
>
>
> I checked if there  is update transaction on master table involved in
> partition.
> Postgresql takes  RowExclusiveLock on all partition tables.
>
> constraint exclusion is set to on.

I checked this under the debugger and found that only the partitions
which are scanned. The partitions excluded by constraints are not
locked.

postgres=# create table t1 (a int);
CREATE TABLE
postgres=# set constraint_exclusion to partition;
SET
postgres=# create table t1_p1() inherits (t1);
CREATE TABLE
postgres=# alter table t1_p1 add constraint a_part check (a > 0 and a < 100);
ALTER TABLE
postgres=# create table t1_p2() inherits (t1);
CREATE TABLE
postgres=# alter table t1_p2 add constraint a_part check (a > 100 and a < 200);
ALTER TABLE
postgres=# insert into t1_p1 select i from generate_series(1, 5) i;
INSERT 0 5
postgres=# insert into t1_p2 select i from generate_series(101, 105) i;
INSERT 0 5
postgres=# explain verbose select * from t1 where a > 100;                            QUERY PLAN
---------------------------------------------------------------------Append  (cost=0.00..41.88 rows=851 width=4)  ->
SeqScan on public.t1  (cost=0.00..0.00 rows=1 width=4)        Output: t1.a        Filter: (t1.a > 100)  ->  Seq Scan on
public.t1_p2 (cost=0.00..41.88 rows=850 width=4)        Output: t1_p2.a        Filter: (t1_p2.a > 100) 
(7 rows)

postgres=# explain verbose update t1 set a = a where a > 100;                             QUERY PLAN
----------------------------------------------------------------------Update on public.t1  (cost=0.00..41.88 rows=851
width=10) Update on public.t1  Update on public.t1_p2  ->  Seq Scan on public.t1  (cost=0.00..0.00 rows=1 width=10)
  Output: t1.a, t1.ctid        Filter: (t1.a > 100)  ->  Seq Scan on public.t1_p2  (cost=0.00..41.88 rows=850 width=10)
      Output: t1_p2.a, t1_p2.ctid        Filter: (t1_p2.a > 100) 
(9 rows)

The RowExclusiveLock is taken in InitPlan(), which is called after the
partitions have been excluded.
817│                 foreach(l, resultRelations)818│                 {819│                         Index
resultRelationIndex= 
lfirst_int(l);820│                         Oid                     resultRelationOid;821│
Relation       resultRelation;822│823│                         resultRelationOid = 
getrelid(resultRelationIndex, rangeTable);824├>                        resultRelation =
heap_open(resultRelationOid, RowExclusiveLock);825│                         InitResultRelInfo(resultRelInfo,826│
                                                  resultRelation,827│ 
resultRelationIndex,828│
estate->es_instrument);829│                         resultRelInfo++;830│                 }

It does lock the parent table, since inheritance allows to have rows
in that table. If the constraints on that table are not enough to
exclude it by conditions, it will be scanned.

Am I missing something? It might help to have SQL commands you are
running. Also, can you please explain why do you think all the
partitions are locked in RowExclusiveLock mode.



Re: Why postgres take RowExclusiveLock on all partition

From
Sachin Kotwal
Date:
Hi Ashutosh,

Thanks for reply.

Below are my findings:


In 1 Terminal:

postgres=# create table t1 (a int, b int);
CREATE TABLE
postgres=# show constraint_exclusion ;
 constraint_exclusion 
----------------------
 partition
(1 row)
postgres=# create table t1_p1() inherits (t1);
CREATE TABLE
postgres=# alter table t1_p1 add constraint a_part check (a > 0 and a < 100);
ALTER TABLE
postgres=# create table t1_p2() inherits (t1);
CREATE TABLE
postgres=# alter table t1_p2 add constraint a_part check (a > 100 and a < 200);
ALTER TABLE
postgres=# insert into t1_p1 select i,i from generate_series(1, 5) i;
INSERT 0 5
postgres=# insert into t1_p2 select i,i from generate_series(101, 105) i;
INSERT 0 5
postgres=# select * from t1_p1;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 5 | 5
(5 rows)
postgres=# select * from t1_p2;
  a  |  b  
-----+-----
 101 | 101
 102 | 102
 103 | 103
 104 | 104
 105 | 105
(5 rows)
postgres=# begin;
BEGIN
postgres=# update t1 set b=555 where a=101;
UPDATE 1


In another Terminal :

postgres=# select locktype, database::regclass , relation::regclass,virtualtransaction, pid, mode , granted from pg_locks where locktype='relation';
 locktype | database | relation | virtualtransaction |  pid  |       mode       | granted 
----------+----------+----------+--------------------+-------+------------------+---------
 relation | 13241    | pg_locks | 3/3867             | 28635 | AccessShareLock  | t
 relation | 13241    | t1_p2    | 2/14038            | 28633 | RowExclusiveLock | t
 relation | 13241    | t1_p1    | 2/14038            | 28633 | RowExclusiveLock | t
 relation | 13241    | t1       | 2/14038            | 28633 | RowExclusiveLock | t
(4 rows)


Hope above findings will help you to understand problem.


Regards,
Sachin


On Fri, Sep 16, 2016 at 6:20 PM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:
On Fri, Sep 16, 2016 at 4:31 PM, Sachin Kotwal <kotsachin@gmail.com> wrote:
> Hi Hackers,
>
>
> I checked if there  is update transaction on master table involved in
> partition.
> Postgresql takes  RowExclusiveLock on all partition tables.
>
> constraint exclusion is set to on.

I checked this under the debugger and found that only the partitions
which are scanned. The partitions excluded by constraints are not
locked.

postgres=# create table t1 (a int);
CREATE TABLE
postgres=# set constraint_exclusion to partition;
SET
postgres=# create table t1_p1() inherits (t1);
CREATE TABLE
postgres=# alter table t1_p1 add constraint a_part check (a > 0 and a < 100);
ALTER TABLE
postgres=# create table t1_p2() inherits (t1);
CREATE TABLE
postgres=# alter table t1_p2 add constraint a_part check (a > 100 and a < 200);
ALTER TABLE
postgres=# insert into t1_p1 select i from generate_series(1, 5) i;
INSERT 0 5
postgres=# insert into t1_p2 select i from generate_series(101, 105) i;
INSERT 0 5
postgres=# explain verbose select * from t1 where a > 100;
                             QUERY PLAN
---------------------------------------------------------------------
 Append  (cost=0.00..41.88 rows=851 width=4)
   ->  Seq Scan on public.t1  (cost=0.00..0.00 rows=1 width=4)
         Output: t1.a
         Filter: (t1.a > 100)
   ->  Seq Scan on public.t1_p2  (cost=0.00..41.88 rows=850 width=4)
         Output: t1_p2.a
         Filter: (t1_p2.a > 100)
(7 rows)

postgres=# explain verbose update t1 set a = a where a > 100;
                              QUERY PLAN
----------------------------------------------------------------------
 Update on public.t1  (cost=0.00..41.88 rows=851 width=10)
   Update on public.t1
   Update on public.t1_p2
   ->  Seq Scan on public.t1  (cost=0.00..0.00 rows=1 width=10)
         Output: t1.a, t1.ctid
         Filter: (t1.a > 100)
   ->  Seq Scan on public.t1_p2  (cost=0.00..41.88 rows=850 width=10)
         Output: t1_p2.a, t1_p2.ctid
         Filter: (t1_p2.a > 100)
(9 rows)

The RowExclusiveLock is taken in InitPlan(), which is called after the
partitions have been excluded.

 817│                 foreach(l, resultRelations)
 818│                 {
 819│                         Index           resultRelationIndex =
lfirst_int(l);
 820│                         Oid                     resultRelationOid;
 821│                         Relation        resultRelation;
 822│
 823│                         resultRelationOid =
getrelid(resultRelationIndex, rangeTable);
 824├>                        resultRelation =
heap_open(resultRelationOid, RowExclusiveLock);
 825│                         InitResultRelInfo(resultRelInfo,
 826│                                                           resultRelation,
 827│
resultRelationIndex,
 828│
estate->es_instrument);
 829│                         resultRelInfo++;
 830│                 }

It does lock the parent table, since inheritance allows to have rows
in that table. If the constraints on that table are not enough to
exclude it by conditions, it will be scanned.

Am I missing something? It might help to have SQL commands you are
running. Also, can you please explain why do you think all the
partitions are locked in RowExclusiveLock mode.



--

Thanks and Regards,
Sachin Kotwal

Re: Why postgres take RowExclusiveLock on all partition

From
Tom Lane
Date:
Sachin Kotwal <kotsachin@gmail.com> writes:
> In another Terminal :

> postgres=# select locktype, database::regclass ,
> relation::regclass,virtualtransaction, pid, mode , granted from pg_locks
> where locktype='relation';
>  locktype | database | relation | virtualtransaction |  pid  |       mode
>     | granted
> ----------+----------+----------+--------------------+-------+------------------+---------
>  relation | 13241    | pg_locks | 3/3867             | 28635 |
> AccessShareLock  | t
>  relation | 13241    | t1_p2    | 2/14038            | 28633 |
> RowExclusiveLock | t
>  relation | 13241    | t1_p1    | 2/14038            | 28633 |
> RowExclusiveLock | t
>  relation | 13241    | t1       | 2/14038            | 28633 |
> RowExclusiveLock | t
> (4 rows)

The planner must take some type of lock on each partition, because it
has to examine that table and decide whether or not it needs to be
scanned, and that at least requires locking the table's DDL state.
So those locks will be there whether or not the query ultimately scans
the tables.  This isn't a bug.
        regards, tom lane



Re: Why postgres take RowExclusiveLock on all partition

From
Sachin Kotwal
Date:
Hi Tom,


Thanks for reply.

To take decision it should get locks for very small interval.
Does it release locks after taking decision and then perform actual update operation on partition table?
I feel update operation can take longer time than planner to examine and will not require lock in later stage of query execution.

Locking all partition tables leads to blocking all queries(mostly select * ... kind of)  who want lock on other partition.
If we able to release lock immediately  after planner examination it will help to get locks to other running queries on other partitions.

If will be happy we will be able to reduce locking in above scenario.


Regards,
Sachin

On Fri, Sep 16, 2016 at 7:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Sachin Kotwal <kotsachin@gmail.com> writes:
> In another Terminal :

> postgres=# select locktype, database::regclass ,
> relation::regclass,virtualtransaction, pid, mode , granted from pg_locks
> where locktype='relation';
>  locktype | database | relation | virtualtransaction |  pid  |       mode
>     | granted
> ----------+----------+----------+--------------------+-------+------------------+---------
>  relation | 13241    | pg_locks | 3/3867             | 28635 |
> AccessShareLock  | t
>  relation | 13241    | t1_p2    | 2/14038            | 28633 |
> RowExclusiveLock | t
>  relation | 13241    | t1_p1    | 2/14038            | 28633 |
> RowExclusiveLock | t
>  relation | 13241    | t1       | 2/14038            | 28633 |
> RowExclusiveLock | t
> (4 rows)

The planner must take some type of lock on each partition, because it
has to examine that table and decide whether or not it needs to be
scanned, and that at least requires locking the table's DDL state.
So those locks will be there whether or not the query ultimately scans
the tables.  This isn't a bug.

                        regards, tom lane



--

Thanks and Regards,
Sachin Kotwal

Re: Why postgres take RowExclusiveLock on all partition

From
Tom Lane
Date:
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



Re: Why postgres take RowExclusiveLock on all partition

From
Sachin Kotwal
Date:
Hi Tom,

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



--

Thanks and Regards,
Sachin Kotwal

Re: Why postgres take RowExclusiveLock on all partition

From
Bruce Momjian
Date:
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 +