Re: Proposal for syntax to support creation of partition tables when creating parent table

On Wed, Sep 25, 2019 at 8:53 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Muhammad Usama <m(dot)usama(at)gmail(dot)com> writes:
> > I want to propose an extension to CREATE TABLE syntax to allow the
> creation
> > of partition tables along with its parent table using a single statement.
>
> TBH, I think this isn't a particularly good idea. It seems very
> reminiscent of the variant of CREATE SCHEMA that lets you create
> a bunch of contained objects along with the schema. That variant
> is a mess to support and AFAIK it's practically unused in the
> real world. (If it were used, we'd get requests to support more
> than the small number of object types that the CREATE SCHEMA
> grammar currently allows.)
>

IMO creating auto-partitions shouldn't be viewed as creating bunch of
schema objects with CREATE SCHEMA command. Most of the other RDBMS
solutions support the table partition syntax where parent partition table
is specified with partitions and sub-partitions in same SQL statement. As I
understand the proposal is not changing the syntax of creating partitions,
it is providing the ease of creating parent partition table along with its
partitions in same statement. I think it does make it easier when you are
creating a big partition table with lots of partitions and sub-partitions.

The would also benefit users migrating to postgres from Oracle or mysql etc
where similar syntax is supported.

And if not more I think it is a tick in the box with minimal code change.

>
> As Fabien noted, there's been some related discussion about this
> area, but nobody was advocating a solution of this particular shape.
>

The thread that Usama mentioned in his email is creating auto-partitions
just for HASH partitions, this is trying to do similar for all types of
partitions.

> regards, tom lane
>
>
>

--
Highgo Software (Canada/China/Pakistan)
URL : http://www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
EMAIL: mailto: ahsan(dot)hadi(at)highgo(dot)ca