Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

From: Justin Pryzby <[email-masked]>
Date: Mon, 15 Jun 2026 06:07:15 -0500

I hit an error when I tried this patch.

CREATE TABLE a (a text) PARTITION BY RANGE(a);
CREATE TABLE a1 PARTITION OF a DEFAULT;
INSERT INTO a SELECT repeat('1', 9999999);
CREATE TABLE a2 PARTITION OF a FOR VALUES FROM (2)TO(3);
ALTER TABLE a MERGE PARTITIONS (a1, a2) INTO a1;
ERROR:  row is too big: size 39264, maximum size 8160

-- 
Justin

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 15 Jun 2026 23:50:07 +0300

> ERROR:  row is too big: size 39264, maximum size 8160

Thank you for your report.  It appears that createPartitionTable()
misses NewRelationCreateToastTable() call (for instance, DefineTable()
has this call).  The attached patch implements fix and has tests.  I'm
going to push it if no objections.

------
Regards,
Alexander Korotkov
Supabase

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 16 Jun 2026 01:36:08 +0400

Hi, Alexander!

> going to push it if no objections.

It looks like the patch is missing in the last message.

Regards,
Pavel Borisov,
Supabase

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 16 Jun 2026 00:36:56 +0300

> It looks like the patch is missing in the last message.

Sorry, here it is.

------
Regards,
Alexander Korotkov
Supabase

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 16 Jun 2026 01:56:50 +0400

Hi, Alexander!

I looked into the patch. It's simple enough and looks good to me.
I have one question: is it correct to pass 0 reloptions to
NewRelationCreateToastTable or we need to use transformRelOptions +
heap_reloptions pair to derive toast_reloptions as it is done in
ProcessUtilitySlow() and create_ctas_internal()?

Regards,
Pavel Borisov

---

From: jian he <[email-masked]>
Date: Tue, 16 Jun 2026 16:08:39 +0800

> > It looks like the patch is missing in the last message.

src5=# explain (analyze, timing, costs off) SELECT repeat('1', 9999999);
                     QUERY PLAN
-----------------------------------------------------
 Result (actual time=0.002..0.003 rows=1.00 loops=1)
 Planning Time: 1454.745 ms
 Execution Time: 0.035 ms
(3 rows)

Time: 1458.413 ms (00:01.458)

The timing above is from an Assert-enabled build.
Could we optimize these tests to make them less expensive to run?

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

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 16 Jun 2026 15:19:13 +0300

> src5=# explain (analyze, timing, costs off) SELECT repeat('1', 9999999);

Yes, we can.  Here is the second version of this patch.  Now it uses
just 10'000 bytes value with external storage.

------
Regards,
Alexander Korotkov
Supabase

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 16 Jun 2026 15:22:02 +0300

Hi, Pavel!

> ProcessUtilitySlow() and create_ctas_internal()?

Yes, (Datum) 0 is correct here.  MERGE/SPLIT PARTITION grammar
provides no syntactic way for the user to specify reloptions for the
new partitions, so there is nothing to extract via
transformRelOptions.  This matches createPartitionTable() itself,
which already calls heap_create_with_catalog(... reloptions=(Datum) 0
...) for the main relation a few lines above; the TOAST table just
inherits that choice.

Extending the syntax to accept WITH (...) on new partitions would be a
separate, larger feature.  Out of scope for this bug fix.

------
Regards,
Alexander Korotkov
Supabase

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 16 Jun 2026 17:01:34 +0400

Hi, Alexander!

> separate, larger feature.  Out of scope for this bug fix.

I agree, this is out of scope of a fix.
Updated test in v2 and overall patch looks good to me.

A couple of wording proposals:
never gave it -> never gives it
Merged partitions need their own TOAST table -> A merged partition
needs its own TOAST table.
re-stored -> stored
a short string suffices -> a string over that threshold suffices

Regards,
Pavel Borisov,
Supabase

---

From: Alexander Korotkov <[email-masked]>
Date: Thu, 18 Jun 2026 10:31:11 +0300

Hi, Pavel!

> a short string suffices -> a string over that threshold suffices

Pushed with your suggestions accepted.

------
Regards,
Alexander Korotkov
Supabase

---

From: Justin Pryzby <[email-masked]>
Date: Thu, 18 Jun 2026 10:49:38 -0500

> Pushed with your suggestions accepted.

Thanks.  When I went back to test this, I merged ~25 partitions that
were all on the same tablespace, but the merged table was created on the
default tablespace.

I tried again with default_tablespace set, but it was ignored.  I think
that's wrong.  It's good to follow the tablespace of the parent table,
but if it has no tablespace set, default_tablespace should be obeyed.
See surrounding logic in DefineRelation.

I see the docs say this:
+       <command>ALTER TABLE MERGE PARTITION</command> uses the partitioned
+       table itself as the template to construct the new partition.
+       The new partition will inherit the same table access method, persistence
+       type, and tablespace as the partitioned table.

-- 
Justin

---

From: Alexander Korotkov <[email-masked]>
Date: Sun, 21 Jun 2026 15:11:26 +0300

> +       type, and tablespace as the partitioned table.

Correct, please see the attached patch, it makes
createPartitionTable() deal with tablespaces the same was as
DefineRelation() does.

------
Regards,
Alexander Korotkov
Supabase

---

From: Pavel Borisov <[email-masked]>
Date: Mon, 22 Jun 2026 14:44:33 +0400

Hi, Alexander!

> createPartitionTable() deal with tablespaces the same was as

Thank you for working on this feature!
I've looked into the last v1 patch.

Does it also worth inheriting DefineRelation()'s check and error for
the case if (tablespaceId == GLOBALTABLESPACE_OID)?
A brief look for default_tablespace GUC doesn't reveal a way why it
could not be set to global tablespace in a session.

Best regards,
Pavel Borisov
Supabase

---

From: Alexander Korotkov <[email-masked]>
Date: Wed, 24 Jun 2026 13:24:49 +0300

> could not be set to global tablespace in a session.

Thank you for catching this.  I've added this check and corresponding tests.

------
Regards,
Alexander Korotkov
Supabase

---

From: Pavel Borisov <[email-masked]>
Date: Wed, 24 Jun 2026 14:44:35 +0400

Hi, Alexander!

> Thank you for catching this.  I've added this check and corresponding tests.

Looked at the patch v2.
I think in test we'd better split comments for the failing block (SET
default_tablespace TO pg_global;) and for the successful block (after
RESET default_tablespace;). Otherwise, the comment paragraph looks a
little hard to read to me:
+-- Parent has no explicit tablespace and default_tablespace is empty: the
+-- new partition uses the database default (reltablespace = 0).  Also
+-- exercise the pg_global rejection path with default_tablespace pointing
+-- at the shared tablespace.

Overall the patch looks good to me.

Regards,
Pavel Borisov
Supabase

---

From: Justin Pryzby <[email-masked]>
Date: Fri, 26 Jun 2026 08:40:14 -0500

Thanks for addressing the tablespace issue.

One more thing -- merging non-contiguous tables is currently refused:

ts=# CREATE TABLE a(t timestamptz) PARTITION BY RANGE (t);
ts=# CREATE TABLE a_20260101 PARTITION OF a FOR VALUES FROM ('20260101')TO('20260102');
ts=# CREATE TABLE a_20260126 PARTITION OF a FOR VALUES FROM ('20260126')TO('20260127');
ts=# CREATE TABLE a_20260127 PARTITION OF a FOR VALUES FROM ('20260127')TO('20260128');
ts=# CREATE TABLE a_20260128 PARTITION OF a FOR VALUES FROM ('20260128')TO('20260129');

ts=# ALTER TABLE a MERGE PARTITIONS (a_20260101,a_20260126,a_20260127,a_20260128) INTO a_202601;
ERROR:  cannot merge partition "a_20260126" together with partition "a_20260101"
DETAIL:  The lower bound of partition "a_20260126" is not equal to the upper bound of partition "a_20260101".
HINT:  ALTER TABLE ... MERGE PARTITIONS requires the partition bounds to be adjacent.

The goal seems to be to avoid overlapping partition constaints.

If there's a default partition, that also avoids the possibility of
needing to move *some* (but not all) tuples from it into the merged
partition.

I think there should be separate logic depending on the existance of a
default partition:

- The existing logic seems to correctly handle the case of a default
  partition, which is being merged (gap is allowed).

- The case of a default partition which is not being merged is also
  handled: no gap is allowed.  Actually, this case was probably the
  motivation behind checking that the partitions are adjacent.

- If there's *no* default partition, then I think the check should be
  relaxed; it's sufficient to verify that the bounds of the merged
  partition do not overlap with any partition which is not being merged;

-- 
Justin

---

From: Pavel Borisov <[email-masked]>
Date: Fri, 26 Jun 2026 17:47:06 +0400

Hi, Justin!

>   partition do not overlap with any partition which is not being merged;

Thank you for reporting on this feature!
Yes, I think main motivation for not allowing a partition bounds gaps for
merge was the complication in case a DEFAULT partition exists.
Probably, this could be relaxed when there is no DEFAULT partition.

Regards,
Pavel Borisov
Supabase

---

From: Dmitry Koval <[email-masked]>
Date: Fri, 26 Jun 2026 18:20:18 +0300

Hi!

If we exclude case with DEFAULT partition, MERGE PARTITIONS command was
intended to be used when an incorrect table partitioning was chosen.
For example, a table was initially partitioned by month, but later we
needed to change table partitioning by quarter. In this case, MERGE
PARTITIONS command should merge several adjacent partitions into one.
Current checks are made for this case.

 >partition do not overlap with any partition which is not being merged;

It's probably possible to do this. But in this case, the command will
not exactly be "MERGE PARTITIONS" ("MERGE PARTITIONS & EXPAND"?).

-- 
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com

---

From: Alexander Korotkov <[email-masked]>
Date: Fri, 26 Jun 2026 22:50:36 +0300

> not exactly be "MERGE PARTITIONS" ("MERGE PARTITIONS & EXPAND"?).

+1,
We can implement a support for this for 20.  For 19, I propose to
state the restriction more explicit in the docs.  See the attached
patch.

------
Regards,
Alexander Korotkov
Supabase

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 30 Jun 2026 14:06:53 +0300

> state the restriction more explicit in the docs.  See the attached

I'm going to push the docs patch if no objections.

------
Regards,
Alexander Korotkov
Supabase

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 30 Jun 2026 15:25:31 +0400

Hi, Alexander!

> I'm going to push the docs patch if no objections.
I looked at the doc patch. IMO "in order to be merged" is redundant.
I'd propose following:
"For range-partitioned tables, the ranges of merged partitions must be
adjacent. This applies even if the partitioned table has no default
partition."

Regards,
Pavel Borisov
Supabase

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 30 Jun 2026 22:38:00 +0300

Hi, Pavel!

> adjacent. This applies even if the partitioned table has no default

I've included your suggestion.
Pushed and and backpatched to 19.

------
Regards,
Alexander Korotkov
Supabase