Re: [HACKERS] Why postgres take RowExclusiveLock on all partition

2016-09-22 Thread Bruce Momjian
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  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 +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why postgres take RowExclusiveLock on all partition

2016-09-16 Thread Sachin Kotwal
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  wrote:

> Sachin Kotwal  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: [HACKERS] Why postgres take RowExclusiveLock on all partition

2016-09-16 Thread Tom Lane
Sachin Kotwal  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why postgres take RowExclusiveLock on all partition

2016-09-16 Thread Sachin Kotwal
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  wrote:

> Sachin Kotwal  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: [HACKERS] Why postgres take RowExclusiveLock on all partition

2016-09-16 Thread Tom Lane
Sachin Kotwal  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why postgres take RowExclusiveLock on all partition

2016-09-16 Thread Sachin Kotwal
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.ba...@enterprisedb.com> wrote:

> On Fri, Sep 16, 2016 at 4:31 PM, Sachin Kotwal 
> 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│ RelationresultRelation;
>  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 

Re: [HACKERS] Why postgres take RowExclusiveLock on all partition

2016-09-16 Thread Ashutosh Bapat
On Fri, Sep 16, 2016 at 4:31 PM, Sachin Kotwal  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│ RelationresultRelation;
 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.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers