Message-ID: <87b560e3-c5fa-ab78-4109-20f8aa24f5cc@postgrespro.ru>
Date: Tue, 31 May 2022 23:22:32 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.1
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Content-Language: en-US
To: Laurenz Albe <laurenz.albe@cybertec.at>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <163714495450.16056.3566953095730878367@malur.postgresql.org>
 <c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582@postgrespro.ru>
 <ca74193b9b8c8906e4ba761de74e7c030ba50877.camel@cybertec.at>
From: Dmitry Koval <d.koval@postgrespro.ru>
In-Reply-To: <ca74193b9b8c8906e4ba761de74e7c030ba50877.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/87b560e3-c5fa-ab78-4109-20f8aa24f5cc%40postgrespro.ru>
Precedence: bulk

> I didn't read the patch, but what lock level does that place on the
> partitioned table?  Anything more than ACCESS SHARE?

Current patch locks a partitioned table with ACCESS EXCLUSIVE lock. 
Unfortunately only this lock guarantees that other session can not work 
with partitions that are splitting or merging.

I want add CONCURRENTLY mode in future. With this mode partitioned table 
during SPLIT/MERGE operation will be locked with SHARE UPDATE EXCLUSIVE 
(as ATTACH/DETACH PARTITION commands in CONCURRENTLY mode).
But in this case queries from other sessions that want to work with 
partitions that are splitting/merging at this time should receive an 
error (like "Partition data is moving. Repeat the operation later") 
because old partitions will be deleted at the end of SPLIT/MERGE operation.
I hope exists a better solution, but I don't know it now...

-- 
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com