Skip site navigation (1) Skip section navigation (2)

Syntax for partitioning

From: Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Syntax for partitioning
Date: 2009-10-29 02:15:31
Message-ID: 20091029111531.96CD.52131E4D@oss.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackers
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/bd8134a40906080702s96c90a9q3bbb581b9bd0d5d7@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.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center


Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2009-10-29 04:39:58
Subject: Re: Syntax for partitioning
Previous:From: Robert HaasDate: 2009-10-29 02:10:53
Subject: Re: WIP: push AFTER-trigger execution into ModifyTable node

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group