On Tue, Sep 20, 2022 at 3:21 PM Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Sep 19, 2022 at 4:42 PM Dmitry Koval <d.ko...@postgrespro.ru> wrote: > > 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. > > Yeah, me neither. > > > 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). > > I think that a built-in DDL command can't really assume that the user > won't modify anything. You'd have to take a ShareLock. > > But you might be able to have a CONCURRENTLY variant of the command > that does the same kind of multi-transaction thing as, e.g., CREATE > INDEX CONCURRENTLY. You would probably have to be quite careful about > race conditions (e.g. you commit the first transaction and before you > start the second one, someone drops or detaches the partition you were > planning to merge or split). Might take some thought, but feels > possibly doable. I've never been excited enough about this kind of > thing to want to put a lot of energy into engineering it, because > doing it "manually" feels so much nicer to me, and doubly so given > that we now have ATTACH CONCURRENTLY and DETACH CONCURRENTLY, but it > does seem like a thing some people would probably use and value.
+1 Currently people are using external tools to implement this kind of task. However, having this functionality in core would be great. Implementing concurrent merge/split seems quite a difficult task, which needs careful design. It might be too hard to carry around the syntax altogether. So, I think having basic syntax in-core is a good step forward. But I think we need a clear notice in the documentation about the concurrency to avoid wrong user expectations. ------ Regards, Alexander Korotkov