Re: MERGE/SPLIT partition commands should create new partitions in the parent's tablespace?

On Sat, Jul 6, 2024 at 4:06 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)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.

While the patch does not include test cases for SPLIT PARTITIONS,
which is understandable as these commands use the common function that
we have fixed, I believe it would be prudent to test SPLIT PARTITIONS
as well since we could change it in the future development.

Regards,

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