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

On Wed, Jul 10, 2024 at 4:14 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>
>
>
> On 2024/07/10 12:13, Masahiko Sawada wrote:
> > 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.
>
> Thanks for the review!
>
>
> > 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.
>
> Unless I'm mistaken, the patch already includes tests for the split case.
> Could you please check the tests added to partition_split.sql?
>

Oops, sorry, I missed that part for some reason.So the patch looks good to me.

Regards,

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