Hi,
We are implementing table partitioning feature to support
- the attached commands. The syntax conforms to most of the suggestion
mentioned in
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php, barring
the following:
-- Specification of partition names is optional. System will be able to
generate partition names in such cases.
-- sub partitioning
We are using pgsql triggers to push/move data to appropriate partitions,
but we will definitely consider moving to C language triggers as suggested
by manu.
- Global non-partitioned indexes (that will extend all the partitions).
- Foreign key support for tables referring to partitioned tables.
Please feel free to post your comments and suggestions.
Thanks,
Amit
Persistent Systems
1. Creating partition table with "Create table" command.
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
[ PARTITION BY [ HASH | LIST | RANGE ] (column_name)
[
PARTITIONS num_hash_partitions |
list_patition [,...] |
range_partition [,...]
]
]
where list_partition is:
[Partition_name] VALUES
[ (const_expression[,...]) | DEFAULT]
Database will generate partition name, if it is not specified.
where range_partition is:
[Partition_name]
[
([START const_expression] END const_expression) |
DEFAULT
]
Database will generate partition name, if it is not specified.
Start key word is optional. Default values can be stored in a 'default'
partition.
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
UNIQUE [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
CHECK (expression) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH
SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON
UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
2. Adding/Splitting/Updating/dropping a partition to an existing table with
ALTER TABLE command.
ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name
ALTER TABLE name
SET SCHEMA new_schema
ALTER TABLE name
ADD PARITION [list_partition | range_partition]
ALTER TABLE name
SPLIT PARITION [Partition_name] AT
VALUES const_expression [,...]
[INTO (Partition_name1, Partition_name2)]
The into clause will allow users to provide names of newly created partitions
after the split.
For list partitioning, Partition_name2 will be created with the list of
specified const_expressions, and
Partition_name will be renamed to Partition_name1 after excluding specified
values from its list.
For range partition, a const_expression will indicate the split point.
Partition_name2 will be formed with
max_value and split_point as the range, and Partition_name1 will be formed with
min_value and split_point
range.
ALTER TABLE name
UPDATE PARTITION
[
PARTITIONS TO num_hash_partitions |
[Partition_name | VALUES (const_expression[,...])] TO VALUES
(const_expression[,...]) |
[Partition_name | START (const_expression) END (const_expression) ] TO
START (const_expression) END (const_expression)
]
To change partition key values for list and range partitioned tables, the user
has to either Specify partition name
of partition list/range that identifies the partition to be updated.
ALTER TABLE name
DROP PARITION
Partition_name |
FOR VALUES (const_expression [,...]) |
FOR ([START const_expression] END const_expression)
ALTER TABLE table_name
RENAME Partition_name to Partition_name
where action is one of:
ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE new_tablespace
Things identified in WIP patch for Autopartition by Nikhil S.
1. CREATE TABLE statement has been modified to add Range and List partitions.
2. Full constraints need to be specified for creating partitions.
3. This implementation does not make use of any catalog tables to store
information about partitions, constraints.
3. “PARTITION BY HASH” is not implemented.
4. Functions like to_date() can be used in the constraints.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
