Re: Adding support for Default partition in partitioning

+1 for AS DEFAULT syntax if it helps in improving readability specially in
following case

CREATE TABLE p1 PARTITION OF test AS DEFAULT PARTITION BY LIST(a);

Thank you,
Rahila Syed

On Tue, May 9, 2017 at 1:13 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, May 4, 2017 at 4:40 PM, Sven R. Kunze <srkunze(at)mail(dot)de> wrote:
> > It yields
> >
> > CREATE TABLE p1 PARTITION OF test DEFAULT PARTITION BY LIST(b);
> >
> > This reads to me like "DEFAULT PARTITION".
> >
> > I can imagine a lot of confusion when those queries are encountered in
> the
> > wild. I know this thread is about creating a default partition but I
> were to
> > propose a minor change in the following direction, I think confusion
> would
> > be greatly avoided:
> >
> > CREATE TABLE p1 PARTITION OF test AS DEFAULT PARTITIONED BY LIST(b);
> >
> > I know it's a bit longer but I think those 4 characters might serve
> > readability in the long term. It was especially confusing to see
> PARTITION
> > in two positions serving two different functions.
>
> Well, we certainly can't make that change just for default partitions.
> I mean, that would be non-orthogonal, right? You can't say that the
> way to subpartition is to write "PARTITION BY strategy" when the table
> unpartitioned or is a non-default partition but "PARTITIONED BY
> strategy" when it is a default partition. That would certainly not be
> a good way of confusing users less, and would probably result in a
> variety of special cases in places like ruleutils.c or pg_dump, plus
> some weasel-wording in the documentation. We COULD do a general
> change from "CREATE TABLE table_name PARTITION BY strategy" to "CREATE
> TABLE table_name PARTITIONED BY strategy". I don't have any
> particular arguments against that except that the current syntax is
> more like Oracle, which might count for something, and maybe the fact
> that we're a month after feature freeze. Still, if we want to change
> that, now would be the time; but I favor leaving it alone.
>
> I don't have a big objection to adding AS. If that's the majority
> vote, fine; if not, that's OK, too. I can see it might be a bit more
> clear in the case you mention, but it might also just be a noise word
> that we don't really need. There don't seem to be many uses of AS
> that would pose a risk of actual grammar conflicts here. I can
> imagine someone wanting to use CREATE TABLE ... PARTITION BY ... AS
> SELECT ... to create and populate a partition in one command, but that
> wouldn't be a conflict because it'd have to go AFTER the partition
> specification. In the DEFAULT case, you'd end up with something like
>
> CREATE TABLE p1 PARTITION OF test AS DEFAULT AS <query>
>
> ...which is neither great nor horrible syntax-wise and maybe not such
> a good thing to support anyway since it would have to lock the parent
> to add the partition and then keep the lock on the parent while
> populating the new child (ouch).
>
> So I guess I'm still in favor of the CREATE TABLE p1 PARTITION OF test
> DEFAULT syntax, but if it ends up being AS DEFAULT instead, I can live
> with that.
>
> Other opinions?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>