public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: jian he <[email protected]>
Cc: Pavel Borisov <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: Dmitry Koval <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: [email protected]
Cc: Tomas Vondra <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Date: Tue, 16 Jun 2026 15:19:13 +0300
Message-ID: <CAPpHfdv8N7cRu9BxN=_9ZewdoseUs7_i_RfsHdkp32kY3zSHVQ@mail.gmail.com> (raw)
In-Reply-To: <CACJufxEu1_pGYaFgiDf8wFjy-T1FBg4nfbnrtvz+mStZLDn2pA@mail.gmail.com>
References: <[email protected]>
<ZjFRSVnvvGrsdndU@pryzbyj2023>
<[email protected]>
<ZjTlQiBPYm707Rxv@pryzbyj2023>
<CAPpHfduhHm+4yeObsj9wK4iZvV2Ve5xcr6BwZDFiNBYbE1PfLg@mail.gmail.com>
<CAPpHfdtgLiT2d++-qLircLB8ATUfzgR=aTmRDdF7w=2E9yDHbQ@mail.gmail.com>
<Zjvwqs-GV0MjWIhU@pryzbyj2023>
<CAPpHfdvh_XYHahtpoxfzDkvYOFUH1F5tm3JTV=sOZt8M4CTBcA@mail.gmail.com>
<ZkN6EfjQGz8TOhbL@pryzbyj2023>
<CAPpHfdvz3S7SoRiaSk9C-Juy2EzHs6NC_c9uUR0i7bymKXkvuQ@mail.gmail.com>
<ai_c4-v8iLA2kXFV@pryzbyj2023>
<CAPpHfdvOvaPxMoUp3T6hf4UmAaOhdPCcw71OGUWDjTXvrTZ_JA@mail.gmail.com>
<CALT9ZEFrDgha=RyOuWru45FTk9Dbj5FF-fTw0NnE8n1FwBb2yg@mail.gmail.com>
<CAPpHfdvpzvNt=d_Csxe-TCtvF9MRNdeMtfJu3RkmTgBkkQbGUw@mail.gmail.com>
<CACJufxEu1_pGYaFgiDf8wFjy-T1FBg4nfbnrtvz+mStZLDn2pA@mail.gmail.com>
On Tue, Jun 16, 2026 at 11:09 AM jian he <[email protected]> wrote:
>
> On Tue, Jun 16, 2026 at 5:37 AM Alexander Korotkov <[email protected]> wrote:
> >
> > On Tue, Jun 16, 2026 at 12:36 AM Pavel Borisov <[email protected]> wrote:
> > > On Tue, 16 Jun 2026 at 00:50, Alexander Korotkov <[email protected]> wrote:
> > > >
> > > > On Mon, Jun 15, 2026 at 2:07 PM Justin Pryzby <[email protected]> wrote:
> > > > >
> > > > > 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
> > > >
> > > > 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.
> > >
> > > 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?
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
Attachments:
[application/octet-stream] v2-0001-Create-TOAST-table-for-partitions-made-by-MERGE-S.patch (7.8K, ../CAPpHfdv8N7cRu9BxN=_9ZewdoseUs7_i_RfsHdkp32kY3zSHVQ@mail.gmail.com/2-v2-0001-Create-TOAST-table-for-partitions-made-by-MERGE-S.patch)
download | inline diff:
From a33137fe9c853f90f48a643963b39ec92017307b Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 15 Jun 2026 15:05:23 +0300
Subject: [PATCH v2] Create TOAST table for partitions made by MERGE/SPLIT
PARTITION
ALTER TABLE ... MERGE PARTITIONS / SPLIT PARTITION builds a new
partition via createPartitionTable(), but never gave it a TOAST table.
When the source rows carried out-of-line varlena values, the move
into the new partition entered heap_toast_insert_or_update() with
reltoastrelid = InvalidOid: the externalization step is skipped, the
value falls back to inline storage and heap_insert() fails with
"row is too big" error. Also, TOAST table is needed if the new partition
receives out-of-line varlena values after the DDL operation is complete.
Call NewRelationCreateToastTable() right after the new partition is
created in createPartitionTable(), mirroring what DefineRelation()
does for regular CREATE TABLE. NewRelationCreateToastTable() decides
on its own whether a TOAST table is actually required, so partitions
with no toast-eligible columns are unaffected.
Reported-by: Justin Pryzby <[email protected]>
Discussion: https://postgr.es/m/ai_c4-v8iLA2kXFV%40pryzbyj2023
Reviewed-by: Pavel Borisov <[email protected]>
Reviewed-by: Jian He <[email protected]>
---
src/backend/commands/tablecmds.c | 9 ++++++
src/test/regress/expected/partition_merge.out | 26 ++++++++++++++++
src/test/regress/expected/partition_split.out | 30 +++++++++++++++++++
src/test/regress/sql/partition_merge.sql | 17 +++++++++++
src/test/regress/sql/partition_split.sql | 19 ++++++++++++
5 files changed, 101 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 38f9ffcd04f..265dcfe7fda 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -22815,6 +22815,15 @@ createPartitionTable(List **wqueue, RangeVar *newPartName,
*/
CommandCounterIncrement();
+ /*
+ * Create a TOAST table if the table needs one. MERGE/SPLIT PARTITION
+ * moves rows from existing partition(s) into new partition(s), which may
+ * carry out-of-line varlena values that the new relation must be able to
+ * store. Also, the new partition must be able to receive out-of-line
+ * varlena values after the DDL operation is complete.
+ */
+ NewRelationCreateToastTable(newRelId, (Datum) 0);
+
/*
* Open the new partition with no lock, because we already have an
* AccessExclusiveLock placed there after creation.
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index d3818f1bf9b..9ee548cbcc8 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -1088,6 +1088,32 @@ SELECT count(*) FROM t WHERE i = 15 AND g IN (SELECT g + 10 FROM t WHERE i = 5);
1
(1 row)
+DROP TABLE t;
+-- Merged partitions need their own TOAST table; otherwise an out-of-line
+-- varlena value carried over from one of the merging partitions has
+-- nowhere to be re-stored. SET STORAGE EXTERNAL forces externalization
+-- for any value over the TOAST threshold, so a short string suffices to
+-- exercise the toast-table dependency.
+CREATE TABLE t (a text) PARTITION BY RANGE(a);
+ALTER TABLE t ALTER COLUMN a SET STORAGE EXTERNAL;
+CREATE TABLE tp_def PARTITION OF t DEFAULT;
+CREATE TABLE tp_2_3 PARTITION OF t FOR VALUES FROM ('2') TO ('3');
+INSERT INTO t SELECT repeat('1', 10000);
+ALTER TABLE t MERGE PARTITIONS (tp_def, tp_2_3) INTO tp_merged;
+SELECT reltoastrelid <> 0 AS has_toast,
+ pg_relation_size(reltoastrelid) > 0 AS toast_used
+ FROM pg_class WHERE relname = 'tp_merged';
+ has_toast | toast_used
+-----------+------------
+ t | t
+(1 row)
+
+SELECT length(a) FROM t;
+ length
+--------
+ 10000
+(1 row)
+
DROP TABLE t;
RESET search_path;
--
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index ff6027af658..9a23657e750 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1653,6 +1653,36 @@ SELECT count(*) FROM t WHERE i = 0 AND tab_id IN (SELECT tab_id FROM t WHERE i =
0
(1 row)
+DROP TABLE t;
+-- Each new partition produced by SPLIT must get its own TOAST table so
+-- that out-of-line varlena attributes coming from the source partition
+-- can be stored. SET STORAGE EXTERNAL forces externalization for any
+-- value over the TOAST threshold, so a short string suffices to exercise
+-- the toast-table dependency.
+CREATE TABLE t (a text) PARTITION BY RANGE(a);
+ALTER TABLE t ALTER COLUMN a SET STORAGE EXTERNAL;
+CREATE TABLE tp_all PARTITION OF t FOR VALUES FROM (MINVALUE) TO (MAXVALUE);
+INSERT INTO t SELECT repeat('1', 10000);
+ALTER TABLE t SPLIT PARTITION tp_all INTO (
+ PARTITION tp_lo FOR VALUES FROM (MINVALUE) TO ('2'),
+ PARTITION tp_hi FOR VALUES FROM ('2') TO (MAXVALUE)
+);
+SELECT relname,
+ reltoastrelid <> 0 AS has_toast,
+ pg_relation_size(reltoastrelid) > 0 AS toast_used
+ FROM pg_class WHERE relname IN ('tp_lo', 'tp_hi') ORDER BY relname;
+ relname | has_toast | toast_used
+---------+-----------+------------
+ tp_hi | t | f
+ tp_lo | t | t
+(2 rows)
+
+SELECT length(a) FROM t;
+ length
+--------
+ 10000
+(1 row)
+
DROP TABLE t;
RESET search_path;
--
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 1e14ed40f5c..2721e648228 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -781,6 +781,23 @@ SELECT count(*) FROM t WHERE i = 15 AND g IN (SELECT g + 10 FROM t WHERE i = 5);
DROP TABLE t;
+-- Merged partitions need their own TOAST table; otherwise an out-of-line
+-- varlena value carried over from one of the merging partitions has
+-- nowhere to be re-stored. SET STORAGE EXTERNAL forces externalization
+-- for any value over the TOAST threshold, so a short string suffices to
+-- exercise the toast-table dependency.
+CREATE TABLE t (a text) PARTITION BY RANGE(a);
+ALTER TABLE t ALTER COLUMN a SET STORAGE EXTERNAL;
+CREATE TABLE tp_def PARTITION OF t DEFAULT;
+CREATE TABLE tp_2_3 PARTITION OF t FOR VALUES FROM ('2') TO ('3');
+INSERT INTO t SELECT repeat('1', 10000);
+ALTER TABLE t MERGE PARTITIONS (tp_def, tp_2_3) INTO tp_merged;
+SELECT reltoastrelid <> 0 AS has_toast,
+ pg_relation_size(reltoastrelid) > 0 AS toast_used
+ FROM pg_class WHERE relname = 'tp_merged';
+SELECT length(a) FROM t;
+DROP TABLE t;
+
RESET search_path;
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index 05de24152d1..48d96e3311e 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -1184,6 +1184,25 @@ SELECT count(*) FROM t WHERE i = 0 AND tab_id IN (SELECT tab_id FROM t WHERE i =
DROP TABLE t;
+-- Each new partition produced by SPLIT must get its own TOAST table so
+-- that out-of-line varlena attributes coming from the source partition
+-- can be stored. SET STORAGE EXTERNAL forces externalization for any
+-- value over the TOAST threshold, so a short string suffices to exercise
+-- the toast-table dependency.
+CREATE TABLE t (a text) PARTITION BY RANGE(a);
+ALTER TABLE t ALTER COLUMN a SET STORAGE EXTERNAL;
+CREATE TABLE tp_all PARTITION OF t FOR VALUES FROM (MINVALUE) TO (MAXVALUE);
+INSERT INTO t SELECT repeat('1', 10000);
+ALTER TABLE t SPLIT PARTITION tp_all INTO (
+ PARTITION tp_lo FOR VALUES FROM (MINVALUE) TO ('2'),
+ PARTITION tp_hi FOR VALUES FROM ('2') TO (MAXVALUE)
+);
+SELECT relname,
+ reltoastrelid <> 0 AS has_toast,
+ pg_relation_size(reltoastrelid) > 0 AS toast_used
+ FROM pg_class WHERE relname IN ('tp_lo', 'tp_hi') ORDER BY relname;
+SELECT length(a) FROM t;
+DROP TABLE t;
RESET search_path;
--
2.39.5 (Apple Git-154)
view thread (174+ 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], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
In-Reply-To: <CAPpHfdv8N7cRu9BxN=_9ZewdoseUs7_i_RfsHdkp32kY3zSHVQ@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