On 29 Oct 2009, at 02:15, Itagaki Takahiro wrote:
I'd like to improve partitioning feature in 8.5.
Kedar-san's previous work is wonderful, but I cannot see any updated
patch.
http://archives.postgresql.org/message-id/bd8134a40906080702s96c90a9q3bbb581b9bd0d...@mail.gmail.com
So, I'll take over the work if there are no ones to do it.
I'm thinking to add syntax support first. Table partitioning was
proposed many times, but it is still not applied into core.
The reason is it is too difficult to make perfect partitioning
feature at once. I think syntax support is a good start.
First, I will add syntax for CREATE TABLE, ALTER TABLE ADD/DROP
PARTITION.
The syntax is borrowed from from Oracle and MySQL. Their
characteristics
are using "LESS THAN" in range partitioning. The keyword "PARTITION"
is
added to the full-reserved keyword list to support ADD/DROP PARTITION.
Those syntax is merely a syntax sugar for INHERITS with CHECK.
Declarations
are translated into CHECK constraints. I have a plan to adjust
pg_dump to
dump definitions of partitioning in the correct format, but the actual
implementation will be still based on constraint exclusion. In
addition,
hash partitioning is not implemented; syntax is parsed but "not
implemented"
error are raised for now.
Here is syntax I propose:
----
ALTER TABLE table_name ADD PARTITION name ...;
ALTER TABLE table_name DROP PARTITION [IF EXISTS] name [CASCADE |
RESTRICT];
Range partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY RANGE ( a_expr )
(
PARTITION name VALUES LESS THAN [(] const [)],
PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow
partition
);
List partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY LIST ( a_expr )
(
PARTITION name VALUES [IN] ( const [, ...] ),
PARTITION name VALUES [IN] [(] DEFAULT [)] -- overflow
partition
);
Hash partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY HASH ( a_expr )
PARTITIONS num_partitions;
CREATE TABLE table_name ( columns )
PARTITION BY HASH ( a_expr )
(
PARTITION name,
...
);
Note:
* Each partition can have optional WITH (...) and TABLESPACE clauses.
* '(' and ')' are optional to support both Oracle and MySQL syntax.
----
Comments welcome.
+1000
Thanks !
(most anticipated feature for 8.5, here, next to replication [well, I
am interested in multi master, but that's not going to happen :P ] )
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers