Re: 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 Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Sachin Kotwal <kotsachin(at)gmail(dot)com> 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