Re: MERGE/SPLIT partition commands should create new partitions in the parent's tablespace? - Mailing list pgsql-hackers

On Wed, Jul 10, 2024 at 5:14 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Jul 10, 2024 at 4:14 PM Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
> >
> >
> >
> > On 2024/07/10 12:13, Masahiko Sawada wrote:
> > > On Sat, Jul 6, 2024 at 4:06 PM Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
> > >>
> > >> Hi,
> > >>
> > >> I noticed that ALTER TABLE MERGE PARTITIONS and SPLIT PARTITION commands
> > >> always create new partitions in the default tablespace, regardless of
> > >> the parent's tablespace. However, the indexes of these new partitions inherit
> > >> the tablespaces of their parent indexes. This inconsistency seems odd.
> > >> Is this an oversight or intentional?
> > >>
> > >> Here are the steps I used to test this:
> > >>
> > >> -------------------------------------------------------
> > >> CREATE TABLESPACE tblspc LOCATION '/tmp/tblspc';
> > >> CREATE TABLE t (i int PRIMARY KEY USING INDEX TABLESPACE tblspc)
> > >>     PARTITION BY RANGE (i) TABLESPACE tblspc;
> > >>
> > >> CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
> > >> CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
> > >>
> > >> ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
> > >>
> > >> SELECT tablename, tablespace FROM pg_tables WHERE tablename IN ('t', 'tp_0_2') ORDER BY tablename;
> > >>    tablename | tablespace
> > >> -----------+------------
> > >>    t         | tblspc
> > >>    tp_0_2    | (null)
> > >> (2 rows)
> > >>
> > >> SELECT indexname, tablespace FROM pg_indexes WHERE tablename IN ('t', 'tp_0_2') ORDER BY indexname;
> > >>     indexname  | tablespace
> > >> -------------+------------
> > >>    t_pkey      | tblspc
> > >>    tp_0_2_pkey | tblspc
> > >> -------------------------------------------------------
> > >>
> > >>
> > >> If it's an oversight, I've attached a patch to ensure these commands create
> > >> new partitions in the parent's tablespace.
> > >
> > > +1
> > >
> > > Since creating a child table through the CREATE TABLE statement sets
> > > its parent table's tablespace as the child table's tablespace, it is
> > > logical to set the parent table's tablespace as the merged table's
> > > tablespace.

One expectation I had for MERGE PARTITION was that if all partition
tables to be merged are in the same tablespace, the merged table is
also created in the same tablespace. But it would be an exceptional
case in a sense, and I agree with the proposed behavior as it's
consistent. It might be a good idea that we can specify the tablespace
for each merged/split table in the future.

BTW the new regression tests don't check the table and index names.
Isn't it better to show table and index names for better
diagnosability?

+-- Check the new partition inherits parent's tablespace
+CREATE TABLE t (i int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace)
+  PARTITION BY RANGE (i) TABLESPACE regress_tblspace;
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+SELECT tablespace FROM pg_tables WHERE tablename IN ('t', 'tp_0_2')
ORDER BY tablespace;
+    tablespace
+------------------
+ regress_tblspace
+ regress_tblspace
+(2 rows)
+
+SELECT tablespace FROM pg_indexes WHERE tablename IN ('t', 'tp_0_2')
ORDER BY tablespace;
+    tablespace
+------------------
+ regress_tblspace
+ regress_tblspace
+(2 rows)
+
+DROP TABLE t;


Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com