Re: MERGE/SPLIT PARTITIONS issues/questions

On Tue, Aug 25, 2026 at 6:17 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Aug 24, 2026 at 6:40 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> > The attached patch (based on v6) is very rough; I will polish it later.
>
> I hate to be a downer here, but I don't really understand why this
> feature got committed (or, well, re-committed) in the first place, and
> I don't understand why it hasn't been reverted yet. What does this
> actually do that anyone would want or find advantageous?
>
> I admit to being a skeptic of this kind of feature on general
> principle, so take everything I have to say here with an
> appropriately-sized amount of salt. But it seems to me that the major
> arguments for a feature like this would be if it either (1) makes the
> new partitions that it creates really good clones of the original
> partition or (2) does something to minimize data movement or (3) finds
> clever ways to reduce the amount of locking required. As to (1), even
> Alexander seems not entirely satisfied with the current behavior and
> proposes that it be changed in a future release, but that's a
> backward-incompatibility that we should be reluctant to introduce. As
> to (2), the source partitions are always copied in their entirety to
> new partitions, which is probably a pretty fair strategy when
> splitting a partition into equal parts or merging roughly equal-size
> partitions, but very non-optimal when the splits or merges are very
> lopsided. The point here isn't that the strategy is horrible but that
> there's no particular intelligence here; you can easily do the same
> thing by hand. As to (3), the patch takes AccessExclusiveLock on the
> partition parent for the entire duration of the operation. This seems
> non-viable in practice. I suspect that essentially 100% of users will
> prefer to quiesce writes to the partition to be split or merged,
> create new partitions with the same data, and then use ATTACH/DETACH
> CONCURRENTLY to do the swap.
>

Hi.

I believe the original design first tries to lock the partitioned
table with AccessExclusiveLock.
Then, later patches incrementally reduce the lock level.

Achieving the same result with ATTACH PARTITION requires the user to scan the
data out, store it somewhere, and then scan it back in. MERGE/SPLIT PARTITION
needs only a single scan. If we can lower the lock level on the
parent table as well
that would make the feature clearly worthwhile.

-------------------------------------
Summary of the attached patch, which is based on the previous v6 patchset:

Reject MERGE/SPLIT PARTITION when the partitioned table or a source partition
has a trigger, or when a source partition has a local constraint (conislocal), a
local index (relispartition = false), a column default or generation expression
differing from the partitioned table's, a different access method, or is
unlogged. Internal triggers count, so this also rejects any partitioned table
involved in a foreign key constraint.

That's a lot of ereport(ERROR) messages; I hope this makes it more bullet-proof.
Claude mentioned that some items are still pending, such as ACLs,
tablespace, and reloptions.
The new partition losing reloptions should not be a big deal?
I'm not so sure about the other two.

--
jian
https://www.enterprisedb.com/