Re: Why postgres take RowExclusiveLock on all partition - Mailing list pgsql-hackers

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.