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

On Wed, Jul 10, 2024 at 9:36 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> 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.

I agree this is a good idea, so I tried to support this feature.

The attached patch v3-0001 is exactly the same as v2-0001, v3-0002 is
a patch for specifying tablespace for each merged/split table.

I'm not sure this addressed David's concern about the tablespace choice
in ca4103025 though.

>
> 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
>
>


--
Regards
Junwang Zhao