Re: Declarative partitioning

On Fri, Jan 22, 2016 at 8:54 AM, Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> I'd like to comment on the one thing and that's the syntax. It seems to me
> we're really trying to reinvent the wheel and come up with our own version
> of the syntax. Is there a particular reason why not to look at the syntax of
> the other databases and adapt as much of the existing syntax as possible?

I'm not sure this is at all standardized. How do we decide whose
syntax to adopt? Oracle's just because it's Oracle? I don't
particularly like inheriting the limitations of Oracle's syntax - such
as at most 2 levels of partitioning, weird VALUES LESS THAN syntax
that doesn't look like anything else we do in PG and has no way to
specify an opclass, etc.

> I think that's for a few reasons - firstly it makes the life much easier for
> the DBAs and users who are either migrating to PostgreSQL or have to manage
> a mix of databases. Secondly, it serves as a valuable source of engineering
> info, preventing the "I haven't thought of this use case" problem.
>
> An example of this is the proposed syntax for adding a partition
>
> CREATE TABLE measurement_fail
> PARTITION OF measurement
> FOR VALUES START ('2006-02-15') END ('2006-03-01');
>
> which seems a bit awkward as both the databases I'm familiar with (Oracle
> and Sybase) use ALTER TABLE to do this
>
> ALTER TABLE measurement
> ADD PARTITION measurement_fail VALUES LESS THAN ( ... )
>
> And so on for the other commands.

One thing to think about is that pg_dump --binary-upgrade needs to
decorate each command that creates a relation. I guess you could
decorate ALTER TABLE with the same stuff we currently apply to CREATE
TABLE but...

> That being said, I entirely agree with Simon (and others) that getting the
> planner part work is the crucial part of the patch. But I also think that a
> proper abstraction (thanks to good syntax) may be a valuable hint how to
> define the catalogs and such.

No argument on that from me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company