Syntax for partitioning
in [PgSql]
|
Prev: [HACKERS] pg_read_file() and non-ascii input file
Next: [HACKERS] Proposal - temporal contrib module
From: Itagaki Takahiro on 29 Oct 2009 23:03 Peter Eisentraut <peter_e(a)gmx.net> wrote: > On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: > > 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 > > ); > > Maybe this needs to mention the actual operator name instead of LESS > THAN, in case the operator is not named < or the user wants to use a > different one. How about to use "sortby" or "index_elem" here? PARTITION BY RANGE '(' sortby-or-index_elem ')' '(' RangePartitions ')' sortby: a_expr USING qual_all_Op opt_nulls_order | a_expr opt_asc_desc opt_nulls_order index_elem: ColId opt_class opt_asc_desc opt_nulls_order | func_expr opt_class opt_asc_desc opt_nulls_order | '(' a_expr ')' opt_class opt_asc_desc opt_nulls_order We should allow only btree operator class here because we need to extract GREATER-THAN-OR-EQUAL operator from LESS THAN. In addition, we will be able to optimize parition search in the future if we restrict a range partition key should be comparable scalar value. Multidimensional partitioning will be implemented with another approach, something like "PARTITION BY GIST", because it would require different oprimization from range partitioning. BTW, "PARTITION BY <pg_am.amname>" crossed my mind here, but it is not well-investigated yet. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Stark on 30 Oct 2009 00:20 On Thu, Oct 29, 2009 at 7:14 PM, Robert Haas <robertmhaas(a)gmail.com> wrote: >> CREATE TABLE does not require PARTITION to be a reserved keyword, >> but there are conflicts in ALTER TABLE ADD/DROP PARTITION: >> >> * ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT] >> * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT] >> >> There are some solutions: Do we need a DROP PARTITION command at all? What would it even do? Drop the partition from the parent table and throw it away in one step? I think in actual practice people usually remove the partition from the parent table first, then do things like archive it before actually throwing it away. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Itagaki Takahiro on 30 Oct 2009 01:07 Greg Stark <gsstark(a)mit.edu> wrote: > >> * ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT] > >> * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT] > > Do we need a DROP PARTITION command at all? What would it even do? Currently no. So, it would be good to treat PARTITION as just a synonym of TABLE. Not only DROP PARTITION but also ALTER PARTITION will work. > CREATE PARTITION name ON table_name > DROP PARTITION name ALTER PARTITION name ... We might need to specify partition keys with another syntax. ALTER TABLE will have only one new command "PARTITION BY". and we reuse TABLE command for PARTITION in other operations. ALTER TABLE table_name PARTITION BY RANGE (expr) (...) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Peter Eisentraut on 30 Oct 2009 04:03 On Thu, 2009-10-29 at 15:19 -0700, Jeff Davis wrote: > I can't help but wonder if the PERIOD type might be better for > representing a partition range. It would make it easier to express and > enforce the constraint that no two partition ranges overlap ;) I can't help but wonder if the period type might better be a generic container for pairs of scalar, totally-ordered types. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= on 30 Oct 2009 10:17
On Thu, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: > I'd like to improve partitioning feature in 8.5. Nice. > Here is syntax I propose: <snip> Is this the same as / similar to Oracle's syntax? IIRC Nikhil's patch was Oracle's syntax, and I prefer having that one instead of inventing our own wheel. Regards, -- Devrim GÃNDÃZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz |