public inbox for [email protected]  

help / color / mirror / Atom feed

From: Robert Haas <[email protected]>
To: Dmitry Koval <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Date: Tue, 20 Sep 2022 08:20:52 -0400
Message-ID: <CA+TgmoZ-vdW-MU4g9uKtXXg3DnFcb1ym34WWLkDXegt4Xq=37w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<CA+TgmoandAsE=Y=0UiYfP=Oyv7Fn2m717PWuPgFw8m9jQQbopg@mail.gmail.com>
	<[email protected]>

On Mon, Sep 19, 2022 at 4:42 PM Dmitry Koval <[email protected]> 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

view thread (163+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
  In-Reply-To: <CA+TgmoZ-vdW-MU4g9uKtXXg3DnFcb1ym34WWLkDXegt4Xq=37w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox