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, >

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

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

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

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

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

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 |

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

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

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