Re: Syntax for partitioning
От | Itagaki Takahiro |
---|---|
Тема | Re: Syntax for partitioning |
Дата | |
Msg-id | 20091112195450.A967.52131E4D@oss.ntt.co.jp обсуждение исходный текст |
Ответ на | Re: Syntax for partitioning (Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>) |
Ответы |
Re: Syntax for partitioning
(Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi>)
Re: Syntax for partitioning (Jaime Casanova <jcasanov@systemguards.com.ec>) |
Список | pgsql-hackers |
I added psql and pg_dump support to Partitioning Syntax patch. Paritioning information can be retrieved with a new system function pg_get_partitiondef(parentRelid). Both psql and pg_dump use it. There are some changes from the last patch. Some of them seem to be a bit ugly. Ideas welcome. * If a table with the same name already exists when a partition is created, the table is re-used as partition. This behavior is required for pg_dump to be simple. * Don't create a new check constraint when a table is attached as partition to a parent table if the child table has constraints with the same definition. This behavior is required for pg_dump not to add duplicated check constraints in repeated dump and restore. * Inheritance is used for partitions, but pg_dump doesn't dump them as inheritance; It dump a child table without inheritance first, and re-add inheritance with ALTER TABLE PARTITION BY. PartitionInfo is added as a DumpableObject in pg_dump. * Dependencies of objects are managed with existing depencency manager except a check constraint to partition values. Partition constraints can be dropped even if the table is still in the partitioning set. A patch attached, and I'll summarize it: ==== Syntax ==== CREATE TABLE parent (...) PARTITION BY { RANGE | LIST } ( key [ USING oprator ] ) ( <partitions> ); ALTER TABLE parent PARTITION BY { RANGE | LIST } ...; CREATE PARTITION partition ON parent VALUES ...; <partitions> := PARTITION name VALUES LESS THAN { range_upper | MAXVALUE } | PARTITION name VALUES IN ( list_value [,...] | DEFAULT ) ==== System Catalog ==== CREATE TABLE pg_partition ( partrelid oid UNIQUE REFARENCES pg_class(oid), partopr oid REFARENCES pg_operatoroid), partkind "char", -- 'R':RANGE or 'L':LIST partkey text -- node dump of the partition key ) WITHOUT OIDS; CREATE TABLE pg_inherits ( inhrelid oid, inhparent oid, inhseqno integer, + inhvalues anyarray -- Non-null if the inheritance is for partitioning. ) WITHOUT OIDS; ==== Sample output from psql ==== =# \d sales_range Table "public.sales_range" Column | Type | Modifiers ---------------+-----------------------------+----------- salesman_id | numeric(5,0) | salesman_name | character varying(30) | sales_state | character varying(20) | sales_date | timestamp without time zone | Partitions: PARTITION BY RANGE ( sales_date USING < ) ( PARTITION sales_2006 VALUES LESS THAN '2007-01-01 00:00:00', ... ) ==== Sample output from pg_dump ==== CREATE TABLE sales_range (...); CREATE TABLE sales_2006 (...); -- without inheritance ALTER TABLE public.sales_range PARTITION BY RANGE ( sales_date USING < ) ( PARTITION sales_2006 VALUES LESS THAN '2007-01-01 00:00:00', ... ); Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Вложения
В списке pgsql-hackers по дате отправления: