Re: [HACKERS] Why postgres take RowExclusiveLock on all partition
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 Momjianhttp://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
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 Lanewrote: > 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
Sachin Kotwalwrites: > 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
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 Lanewrote: > 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
Sachin Kotwalwrites: > 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
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
On Fri, Sep 16, 2016 at 4:31 PM, Sachin Kotwalwrote: > 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