public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: Justin Pryzby <[email protected]>
Cc: Pavel Borisov <[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: Sun, 21 Jun 2026 15:11:26 +0300
Message-ID: <CAPpHfduZ9Yd6vG0EOZUa_MOVxpEDMsZB-89pgNypwFzp2ax+NQ@mail.gmail.com> (raw)
In-Reply-To: <ajQTklv8QArzTp3h@pryzbyj2023>
References: <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>
<CALT9ZEG0YmzH+3M6PixHUNq13pYt+yh8sAgonfaut=g_ij=oZw@mail.gmail.com>
<CAPpHfdtVp5Xxz59Zse+Gg_oWvZPpwjr+gcVtWq-5pTp0Rsh8gQ@mail.gmail.com>
<CALT9ZEG39=w6zvDqpKc479WjmeNGFMwdJxi=Ut7z4JUN386URg@mail.gmail.com>
<CAPpHfdte=WJQnEnEhf8cKZYE1rT-VfQ2mgh=1dCsCCdSYStSow@mail.gmail.com>
<ajQTklv8QArzTp3h@pryzbyj2023>
On Thu, Jun 18, 2026 at 6:49 PM Justin Pryzby <[email protected]> wrote:
> On Thu, Jun 18, 2026 at 10:31:11AM +0300, Alexander Korotkov wrote:
> > 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.
Correct, please see the attached patch, it makes
createPartitionTable() deal with tablespaces the same was as
DefineRelation() does.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v1-0001-Take-into-account-default_tablespace-during-MERGE.patch (15.0K, ../CAPpHfduZ9Yd6vG0EOZUa_MOVxpEDMsZB-89pgNypwFzp2ax+NQ@mail.gmail.com/2-v1-0001-Take-into-account-default_tablespace-during-MERGE.patch)
download | inline diff:
From e2e9bad654a2083dedae1b7420bde1036fb3eaac Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Thu, 18 Jun 2026 21:53:32 +0300
Subject: [PATCH v1] Take into account default_tablespace during MERGE/SPLIT
PARTITION(S)
createPartitionTable() passed the partitioned parent's reltablespace straight
to heap_create_with_catalog(), bypassing the default_tablespace GUC fallback
that DefineRelation() applies for CREATE TABLE ... PARTITION OF. When the
parent had no explicit tablespace (reltablespace = 0), the new partition
unconditionally landed in the database default, even if default_tablespace
was set to something else; merging or splitting a set of partitions that all
lived in a non-default tablespace produced a new partition in the database
default.
Mirror DefineRelation()'s logic: take parent's reltablespace if set,
otherwise check GetDefaultTablespace() (which reads default_tablespace
and normalises pg_default / MyDatabaseTableSpace to InvalidOid). Also
add the CREATE ACL check on the resolved tablespace, matching
DefineRelation()'s behavior.
Update the documentation for MERGE/SPLIT PARTITION to spell out the
tablespace-selection rule explicitly.
Reported-by: Justin Pryzby <[email protected]>
Discussion: https://postgr.es/m/ajQTklv8QArzTp3h%40pryzbyj2023
---
doc/src/sgml/ref/alter_table.sgml | 18 ++++--
src/backend/commands/tablecmds.c | 25 +++++++-
src/test/regress/expected/partition_merge.out | 48 +++++++++++++++
src/test/regress/expected/partition_split.out | 60 +++++++++++++++++++
src/test/regress/sql/partition_merge.sql | 36 +++++++++++
src/test/regress/sql/partition_split.sql | 45 ++++++++++++++
6 files changed, 227 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index dec34337d1a..36c840f68d8 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1233,8 +1233,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
<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.
+ The new partition inherits the table access method and persistence type
+ of the partitioned table. Its tablespace is selected as for a
+ <command>CREATE TABLE ... PARTITION OF</command> command issued
+ without a <literal>TABLESPACE</literal> clause: if the partitioned
+ table has an explicit tablespace, the new partition uses it;
+ otherwise the value of <xref linkend="guc-default-tablespace"/> is
+ taken into account, falling back to the database's default tablespace.
Constraints, column defaults, column generation expressions, identity
columns, indexes, and triggers are copied from the partitioned table to
@@ -1331,8 +1336,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
<command>ALTER TABLE SPLIT PARTITION</command> uses the partitioned
table itself as the template to construct new partitions.
- New partitions will inherit the same table access method, persistence
- type, and tablespace as the partitioned table.
+ New partitions inherit the table access method and persistence type of
+ the partitioned table. Their tablespace is selected as for a
+ <command>CREATE TABLE ... PARTITION OF</command> command issued
+ without a <literal>TABLESPACE</literal> clause: if the partitioned
+ table has an explicit tablespace, the new partitions use it;
+ otherwise the value of <xref linkend="guc-default-tablespace"/> is
+ taken into account, falling back to the database's default tablespace.
</para>
<para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 265dcfe7fda..fcbfb896607 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -22735,6 +22735,7 @@ createPartitionTable(List **wqueue, RangeVar *newPartName,
Relation newRel;
Oid newRelId;
Oid existingRelid;
+ Oid tablespaceId;
TupleDesc descriptor;
List *colList = NIL;
Oid relamId;
@@ -22786,10 +22787,32 @@ createPartitionTable(List **wqueue, RangeVar *newPartName,
errmsg("cannot create a permanent relation as partition of temporary relation \"%s\"",
RelationGetRelationName(parent_rel)));
+ /*
+ * Select the tablespace for the new partition. Mirror the logic that
+ * CREATE TABLE foo PARTITION OF ... uses in DefineRelation: take the
+ * partitioned parent's explicit tablespace if it has one, otherwise take
+ * default_tablespace into account, and finally use the database default.
+ */
+ tablespaceId = parent_relform->reltablespace;
+ if (!OidIsValid(tablespaceId))
+ tablespaceId = GetDefaultTablespace(newPartName->relpersistence, false);
+
+ /* Check permissions except when using database's default */
+ if (OidIsValid(tablespaceId) && tablespaceId != MyDatabaseTableSpace)
+ {
+ AclResult aclresult;
+
+ aclresult = object_aclcheck(TableSpaceRelationId, tablespaceId,
+ GetUserId(), ACL_CREATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_TABLESPACE,
+ get_tablespace_name(tablespaceId));
+ }
+
/* Create the relation. */
newRelId = heap_create_with_catalog(newPartName->relname,
namespaceId,
- parent_relform->reltablespace,
+ tablespaceId,
InvalidOid,
InvalidOid,
InvalidOid,
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 4f42afc3dc7..4586b25efe6 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -1114,6 +1114,54 @@ SELECT length(a) FROM t;
10000
(1 row)
+DROP TABLE t;
+-- Tablespace selection for the new merged partition mirrors
+-- CREATE TABLE ... PARTITION OF: the partitioned root's explicit
+-- tablespace wins; otherwise default_tablespace applies; otherwise the
+-- database default is used.
+CREATE TABLE t (i int) PARTITION BY RANGE(i) TABLESPACE regress_tblspace;
+CREATE TABLE tp_0_5 PARTITION OF t FOR VALUES FROM (0) TO (5);
+CREATE TABLE tp_5_10 PARTITION OF t FOR VALUES FROM (5) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+ALTER TABLE t MERGE PARTITIONS (tp_0_5, tp_5_10) INTO tp_merged;
+SELECT spcname FROM pg_class c LEFT JOIN pg_tablespace s
+ ON c.reltablespace = s.oid WHERE c.relname = 'tp_merged';
+ spcname
+------------------
+ regress_tblspace
+(1 row)
+
+DROP TABLE t;
+-- Parent has no explicit tablespace, but default_tablespace is set: the
+-- new partition lands on default_tablespace.
+CREATE TABLE t (i int) PARTITION BY RANGE(i);
+CREATE TABLE tp_0_5 PARTITION OF t FOR VALUES FROM (0) TO (5);
+CREATE TABLE tp_5_10 PARTITION OF t FOR VALUES FROM (5) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+SET default_tablespace TO regress_tblspace;
+ALTER TABLE t MERGE PARTITIONS (tp_0_5, tp_5_10) INTO tp_merged;
+RESET default_tablespace;
+SELECT spcname FROM pg_class c LEFT JOIN pg_tablespace s
+ ON c.reltablespace = s.oid WHERE c.relname = 'tp_merged';
+ spcname
+------------------
+ regress_tblspace
+(1 row)
+
+DROP TABLE t;
+-- Parent has no explicit tablespace and default_tablespace is empty: the
+-- new partition uses the database default (reltablespace = 0).
+CREATE TABLE t (i int) PARTITION BY RANGE(i);
+CREATE TABLE tp_0_5 PARTITION OF t FOR VALUES FROM (0) TO (5);
+CREATE TABLE tp_5_10 PARTITION OF t FOR VALUES FROM (5) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+ALTER TABLE t MERGE PARTITIONS (tp_0_5, tp_5_10) INTO tp_merged;
+SELECT reltablespace FROM pg_class WHERE relname = 'tp_merged';
+ reltablespace
+---------------
+ 0
+(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 faaf32ed20a..b1df820a177 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1683,6 +1683,66 @@ SELECT length(a) FROM t;
10000
(1 row)
+DROP TABLE t;
+-- Tablespace selection for the new partitions mirrors
+-- CREATE TABLE ... PARTITION OF: the partitioned root's explicit
+-- tablespace wins; otherwise default_tablespace applies; otherwise the
+-- database default is used.
+CREATE TABLE t (i int) PARTITION BY RANGE(i) TABLESPACE regress_tblspace;
+CREATE TABLE tp_all PARTITION OF t FOR VALUES FROM (0) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+ALTER TABLE t SPLIT PARTITION tp_all INTO (
+ PARTITION tp_lo FOR VALUES FROM (0) TO (5),
+ PARTITION tp_hi FOR VALUES FROM (5) TO (10)
+);
+SELECT c.relname, s.spcname FROM pg_class c LEFT JOIN pg_tablespace s
+ ON c.reltablespace = s.oid WHERE c.relname IN ('tp_lo', 'tp_hi')
+ ORDER BY c.relname;
+ relname | spcname
+---------+------------------
+ tp_hi | regress_tblspace
+ tp_lo | regress_tblspace
+(2 rows)
+
+DROP TABLE t;
+-- Parent has no explicit tablespace, but default_tablespace is set: the
+-- new partitions land on default_tablespace.
+CREATE TABLE t (i int) PARTITION BY RANGE(i);
+CREATE TABLE tp_all PARTITION OF t FOR VALUES FROM (0) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+SET default_tablespace TO regress_tblspace;
+ALTER TABLE t SPLIT PARTITION tp_all INTO (
+ PARTITION tp_lo FOR VALUES FROM (0) TO (5),
+ PARTITION tp_hi FOR VALUES FROM (5) TO (10)
+);
+RESET default_tablespace;
+SELECT c.relname, s.spcname FROM pg_class c LEFT JOIN pg_tablespace s
+ ON c.reltablespace = s.oid WHERE c.relname IN ('tp_lo', 'tp_hi')
+ ORDER BY c.relname;
+ relname | spcname
+---------+------------------
+ tp_hi | regress_tblspace
+ tp_lo | regress_tblspace
+(2 rows)
+
+DROP TABLE t;
+-- Parent has no explicit tablespace and default_tablespace is empty: new
+-- partitions use the database default (reltablespace = 0).
+CREATE TABLE t (i int) PARTITION BY RANGE(i);
+CREATE TABLE tp_all PARTITION OF t FOR VALUES FROM (0) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+ALTER TABLE t SPLIT PARTITION tp_all INTO (
+ PARTITION tp_lo FOR VALUES FROM (0) TO (5),
+ PARTITION tp_hi FOR VALUES FROM (5) TO (10)
+);
+SELECT relname, reltablespace FROM pg_class
+ WHERE relname IN ('tp_lo', 'tp_hi') ORDER BY relname;
+ relname | reltablespace
+---------+---------------
+ tp_hi | 0
+ tp_lo | 0
+(2 rows)
+
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 4c8c625f97b..d31ce13dff7 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -798,6 +798,42 @@ SELECT reltoastrelid <> 0 AS has_toast,
SELECT length(a) FROM t;
DROP TABLE t;
+-- Tablespace selection for the new merged partition mirrors
+-- CREATE TABLE ... PARTITION OF: the partitioned root's explicit
+-- tablespace wins; otherwise default_tablespace applies; otherwise the
+-- database default is used.
+CREATE TABLE t (i int) PARTITION BY RANGE(i) TABLESPACE regress_tblspace;
+CREATE TABLE tp_0_5 PARTITION OF t FOR VALUES FROM (0) TO (5);
+CREATE TABLE tp_5_10 PARTITION OF t FOR VALUES FROM (5) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+ALTER TABLE t MERGE PARTITIONS (tp_0_5, tp_5_10) INTO tp_merged;
+SELECT spcname FROM pg_class c LEFT JOIN pg_tablespace s
+ ON c.reltablespace = s.oid WHERE c.relname = 'tp_merged';
+DROP TABLE t;
+
+-- Parent has no explicit tablespace, but default_tablespace is set: the
+-- new partition lands on default_tablespace.
+CREATE TABLE t (i int) PARTITION BY RANGE(i);
+CREATE TABLE tp_0_5 PARTITION OF t FOR VALUES FROM (0) TO (5);
+CREATE TABLE tp_5_10 PARTITION OF t FOR VALUES FROM (5) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+SET default_tablespace TO regress_tblspace;
+ALTER TABLE t MERGE PARTITIONS (tp_0_5, tp_5_10) INTO tp_merged;
+RESET default_tablespace;
+SELECT spcname FROM pg_class c LEFT JOIN pg_tablespace s
+ ON c.reltablespace = s.oid WHERE c.relname = 'tp_merged';
+DROP TABLE t;
+
+-- Parent has no explicit tablespace and default_tablespace is empty: the
+-- new partition uses the database default (reltablespace = 0).
+CREATE TABLE t (i int) PARTITION BY RANGE(i);
+CREATE TABLE tp_0_5 PARTITION OF t FOR VALUES FROM (0) TO (5);
+CREATE TABLE tp_5_10 PARTITION OF t FOR VALUES FROM (5) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+ALTER TABLE t MERGE PARTITIONS (tp_0_5, tp_5_10) INTO tp_merged;
+SELECT reltablespace FROM pg_class WHERE relname = 'tp_merged';
+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 9e44aa9caf0..a39be53d3e2 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -1204,6 +1204,51 @@ SELECT relname,
SELECT length(a) FROM t;
DROP TABLE t;
+-- Tablespace selection for the new partitions mirrors
+-- CREATE TABLE ... PARTITION OF: the partitioned root's explicit
+-- tablespace wins; otherwise default_tablespace applies; otherwise the
+-- database default is used.
+CREATE TABLE t (i int) PARTITION BY RANGE(i) TABLESPACE regress_tblspace;
+CREATE TABLE tp_all PARTITION OF t FOR VALUES FROM (0) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+ALTER TABLE t SPLIT PARTITION tp_all INTO (
+ PARTITION tp_lo FOR VALUES FROM (0) TO (5),
+ PARTITION tp_hi FOR VALUES FROM (5) TO (10)
+);
+SELECT c.relname, s.spcname FROM pg_class c LEFT JOIN pg_tablespace s
+ ON c.reltablespace = s.oid WHERE c.relname IN ('tp_lo', 'tp_hi')
+ ORDER BY c.relname;
+DROP TABLE t;
+
+-- Parent has no explicit tablespace, but default_tablespace is set: the
+-- new partitions land on default_tablespace.
+CREATE TABLE t (i int) PARTITION BY RANGE(i);
+CREATE TABLE tp_all PARTITION OF t FOR VALUES FROM (0) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+SET default_tablespace TO regress_tblspace;
+ALTER TABLE t SPLIT PARTITION tp_all INTO (
+ PARTITION tp_lo FOR VALUES FROM (0) TO (5),
+ PARTITION tp_hi FOR VALUES FROM (5) TO (10)
+);
+RESET default_tablespace;
+SELECT c.relname, s.spcname FROM pg_class c LEFT JOIN pg_tablespace s
+ ON c.reltablespace = s.oid WHERE c.relname IN ('tp_lo', 'tp_hi')
+ ORDER BY c.relname;
+DROP TABLE t;
+
+-- Parent has no explicit tablespace and default_tablespace is empty: new
+-- partitions use the database default (reltablespace = 0).
+CREATE TABLE t (i int) PARTITION BY RANGE(i);
+CREATE TABLE tp_all PARTITION OF t FOR VALUES FROM (0) TO (10);
+INSERT INTO t SELECT generate_series(0, 9);
+ALTER TABLE t SPLIT PARTITION tp_all INTO (
+ PARTITION tp_lo FOR VALUES FROM (0) TO (5),
+ PARTITION tp_hi FOR VALUES FROM (5) TO (10)
+);
+SELECT relname, reltablespace FROM pg_class
+ WHERE relname IN ('tp_lo', 'tp_hi') ORDER BY relname;
+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]
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
In-Reply-To: <CAPpHfduZ9Yd6vG0EOZUa_MOVxpEDMsZB-89pgNypwFzp2ax+NQ@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