Message-ID: <277c8df2-ce99-36b6-817a-ed510c7e8b82@postgrespro.ru>
Date: Mon, 19 Sep 2022 23:42:38 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101
 Thunderbird/91.13.0
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Content-Language: en-US
To: Robert Haas <robertmhaas@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <163714495450.16056.3566953095730878367@malur.postgresql.org>
 <c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582@postgrespro.ru>
 <CA+TgmoandAsE=Y=0UiYfP=Oyv7Fn2m717PWuPgFw8m9jQQbopg@mail.gmail.com>
From: Dmitry Koval <d.koval@postgrespro.ru>
In-Reply-To: 
 <CA+TgmoandAsE=Y=0UiYfP=Oyv7Fn2m717PWuPgFw8m9jQQbopg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/277c8df2-ce99-36b6-817a-ed510c7e8b82%40postgrespro.ru>
Precedence: bulk

Thanks for comments and advice!
I thought about this problem and discussed about it with colleagues.
Unfortunately, I don't know of a good general solution.

19.09.2022 22:56, Robert Haas пишет:
> If you know that a certain partition is not changing, and you would
> like to split it, you can create two or more new standalone tables and
> populate them from the original partition using INSERT .. SELECT. Then
> you can BEGIN a transaction, DETACH the existing partitions, and
> ATTACH the replacement ones. By doing this, you take an ACCESS
> EXCLUSIVE lock on the partitioned table only for a brief period. The
> same kind of idea can be used to merge partitions.

But for specific situation like this (certain partition is not changing) 
we can add CONCURRENTLY modifier.
Our DDL query can be like

ALTER TABLE...SPLIT PARTITION [CONCURRENTLY];

With CONCURRENTLY modifier we can lock partitioned table in 
ShareUpdateExclusiveLock mode and split partition - in 
AccessExclusiveLock mode. So we don't lock partitioned table in 
AccessExclusiveLock mode and can modify other partitions during SPLIT 
operation (except split partition).
If smb try to modify split partition, he will receive error "relation 
does not exist" at end of operation (because split partition will be drop).


-- 
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com