Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

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.

--
Robert Haas
EDB: http://www.enterprisedb.com