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
