public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dmitry Koval <[email protected]>
To: Robert Haas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Date: Mon, 19 Sep 2022 23:42:38 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+TgmoandAsE=Y=0UiYfP=Oyv7Fn2m717PWuPgFw8m9jQQbopg@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CA+TgmoandAsE=Y=0UiYfP=Oyv7Fn2m717PWuPgFw8m9jQQbopg@mail.gmail.com>

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

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: <[email protected]>

* 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