On 18 August 2015 at 18:31, Josh Berkus <j...@agliodbs.com> wrote:
> > 2. Creating a partition of a partitioned table > > > > CREATE TABLE table_name > > PARTITION OF partitioned_table_name > > FOR VALUES values_spec; > > > > Where values_spec is: > > > > listvalues: [IN] (val1, ...) > > > > rangevalues: START (col1min, ... ) END (col1max, ... ) > > | START (col1min, ... ) > > | END (col1max, ... ) > > So, one thing I missed in here is anything about automated partitioning > of tables; that is, creating new partitions based on incoming data or a > simple statement which doesn't require knowledge of the partitioning > scheme. It's possible (and entirely accceptable) that you're > considering automated partition creation outside of the scope of this > patch. I would like to make automatic partitioning outside the scope of this first patch. However, for range partitions, it would be *really* useful to > have this syntax: > > CREATE NEXT PARTITION ON parent_table; > > Which would just create the "next" partition based on whatever the range > partitoning scheme is, instead of requiring the user to calculate start > and end values which might or might not match the parent partitioning > scheme, and might leave gaps. Also this would be useful for range > partitions: > > CREATE PARTITION ON parent_table USING ( start_value ); > > ... where start_value is the start range of the new partition. Again, > easier for users to get correct. > > Both of these require the idea of regular intervals for range > partitions, that is, on a table partitioned by month on a timestamptz > column, each partition will have the range [ month:1, nextmonth:1 ). > This is the most common use-case for range partitions (like, 95% of all > partitioning cases I've seen), so a new partitioning scheme ought to > address it. > > While there are certainly users who desire the ability to define > arbitrary ranges for each range partition, these are by far the minority > and could be accomodated by a different path with more complex syntax. > Further, I'd wager that most users who want to define arbitrary ranges > for range partitions aren't going to be satisfied with the other > restrictions on declarative partitioning (e.g. same constraints, columns > for all partitions) and are going to use inheritance partitioning anyway. I like the idea of a regular partitioning step because it is how you design such tables - "lets use monthly partitions". This gives sanely terse syntax, rather than specifying pages and pages of exact values in DDL.... PARTITION BY RANGE ON (columns) INCREMENT BY (INTERVAL '1 month' ) START WITH value; borrowing the same concepts from sequence syntax. > Creating index on parent is not allowed. They should be defined on (leaf) > > partitions. Because of this limitation, primary keys are not allowed on a > > partitioned table. Perhaps, we should be able to just create a dummy > > entry somewhere to represent an index on parent (which every partition > > then copies.) > > This would be preferable, yes. Making users remember to manually create > indexes on each partition is undesirable. I think it is useful to allow additional indexes on partitions, if desired, but we should always automatically build the indexes that are defined on the master when we create a new partition. Presumably unique indexes will be allowed on partitions. So if the partition key is unique, we can say the whole partitioned table is unique and call that a Primary Key. I would want individual partitions to be placed on separate tablespaces, but not by default. -- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
