Re: Syntax for partitioning

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