Subject: Re: Inadequate executor locking of indexes
References: <19465.1541636036@sss.pgh.pa.us>
 <CAKJS1f9XZ225fZicuPbi_0CLRN64B8+bhxK-hEAgZZHUfzmKZQ@mail.gmail.com>
 <3046.1542990312@sss.pgh.pa.us>
 <e636ac5c-1840-efbb-7fc7-5d95077d7563@lab.ntt.co.jp>
 <CAKJS1f-ibmyn1W_UsdSmygjKOL6YgPyX0Mz54V_iD0HWWL_h=g@mail.gmail.com>
 <4048acfe-82b0-55f3-48d0-9e686b933171@lab.ntt.co.jp>
 <CAKJS1f_+6kzSk_pvEWJD65CN4CP4vdcagk6BxmnJ8KvRBMytuw@mail.gmail.com>
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Message-ID: <31575bce-fbcd-530d-1f97-455c7959cd73@lab.ntt.co.jp>
Date: Tue, 27 Nov 2018 15:00:20 +0900
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.1
MIME-Version: 1.0
In-Reply-To: 
 <CAKJS1f_+6kzSk_pvEWJD65CN4CP4vdcagk6BxmnJ8KvRBMytuw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
        Rushabh Lathia <rushabh.lathia@gmail.com>
Precedence: bulk

On 2018/11/27 6:19, David Rowley wrote:
> On Mon, 26 Nov 2018 at 18:57, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> On 2018/11/26 14:25, David Rowley wrote:
>>> I'm making efforts to delay per-partition work even further in the
>>> executor, for example locking of the per-partition result relations
>>> until after run-time pruning would be a significant win for
>>> partitioned tables with many partitions when generic plans are in use.
>>> Moving things back to InitPlan() flies in the face of that work.
>>>
>>> [1] https://commitfest.postgresql.org/20/1778/
>>
>> That's an interesting point.  Although, considering the concerns that Tom
>> raised about the same index relation being locked such that lock-strength
>> upgrade occurs (his example containing two CTEs), we'll have to find a way
>> to do the ModifyTable run-time pruning such that result relations and
>> their indexes (possibly under multiple ModifyTable nodes) are locked with
>> RowExclusiveLock before they're locked with AccessShareLock lock as scan
>> relations.  For example, we might be able to find a way to do the
>> ModifyTable run-time pruning in InitPlan before initializing plan trees.
> 
> I thought my idea of the processing the rangetable at the end of
> planning to determine the strongest lock per relation would have
> solved that.

Yeah, would be nice to make that work.

Thanks,
Amit