Re: Syntax for partitioning - Mailing list pgsql-hackers

Hi,

Sorry for commenting only now but I think that we need to be able to 
store the partitions in different tablespaces. Even if originally the 
create table creates all partitions in the same tablespace, individual 
partitions should be allowed to be moved in different tablespaces using 
alter table or alter partition.  I think that other databases allows the 
user to define a tablespace for each partition in the create table 
statement.
In a warehouse, you might want to split your partitions on different 
volumes and over time, move older partitions to storage with higher 
compression if that data is not to be accessed frequently anymore. 
Altering tablespaces for partitions is important in that context.

Are you also planning to provide partitioning extensions to 'create 
table as'?

Thanks
Emmanuel

> Here is a WIP partitioning patch. The new syntax are:
>   1. CREATE TABLE parent (...);
>   2. ALTER TABLE parent PARTITION BY { RANGE | LIST } ( key );
>   3. CREATE TABLE child (...);
>   4. ALTER TABLE child INHERIT parent AS PARTITION VALUES ...;
>
> We can also use "CREATE TABLE PARTITION BY" as 1+2+3+4 and
> "CREATE PARTITION" as 3+4. I think "INHERIT AS PARTITION" is rarely
> used typically, but such orthogonality seems to be cleaner.
>
> The most complex logic of the patch is in ATExecAddInherit(). It scans
> existing partitions and generate CHECK constraint for the new partition.
>
> Any comments to the design?  If no objections, I'd like to stop adding
> features in this CommitFest and go for remaining auxiliary works
> -- pg_dump, object dependency checking, documentation, etc.
>
>   
>> -----------------
>>  Catalog changes
>> -----------------
>>     
> In addition to pg_partition, I added pg_inherits.inhvalues field.
> The type of field is "anyarray" and store partition values.
> For range partition, an upper bound value is stored in the array.
> For list partition, list values are stored in it. These separated
> value fields will be useful to implement partition triggers in the
> future. In contrast, reverse engineering of check constraints is messy.
>
> CATALOG(pg_inherits,2611) BKI_WITHOUT_OIDS
> {
>     Oid            inhrelid;
>     Oid            inhparent;
>     int4        inhseqno;
>     anyarray    inhvalues;    /* values for partition */
> } FormData_pg_inherits;
>
>   
>> CREATE TABLE pg_partition (
>>     partrelid oid REFERENCES oid ON pg_class,    -- partitioned table oid
>>     partopr   oid REFERENCES oid ON pg_operator, -- operator to compare keys
>>     partkind  "char", -- kind of partition: 'R' (range) or 'L' (list)
>>     partkey   text,   -- expression tree of partition key
>>     PRIMARY KEY (partrelid)
>> ) WITHOUT OIDS;
>>     
>
> ------------------------------
>  Limitations and Restrictions
> ------------------------------
> * We can create a new partition as long as partitioning keys
>   are not conflicted with existing partitions. Especially,
>   we cannot add any partitions if we have overflow partitions
>   because a new partition always split the overflow partition.
>
> * We cannot reuse an existing check constraint as a partition
>   constraint. ALTER TABLE INHERIT AS PARTITION brings on
>   a table scan to add a new CHECK constraint.
>
> * No partition triggers nor planner and executor improvements.
>   It would come in the future development.
>
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>
>   
> ------------------------------------------------------------------------
>
>
>   


-- 
Emmanuel Cecchet
Aster Data
Web: http://www.asterdata.com