Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

On Tue, Sep 20, 2022 at 3:21 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Sep 19, 2022 at 4:42 PM Dmitry Koval <d(dot)koval(at)postgrespro(dot)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