Thread: Why postgres take RowExclusiveLock on all partition

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.

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

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

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 +