Re: Why postgres take RowExclusiveLock on all partition

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(dot)bapat(at)enterprisedb(dot)com> wrote:

> On Fri, Sep 16, 2016 at 4:31 PM, Sachin Kotwal <kotsachin(at)gmail(dot)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