Re: Auto creation of Partitions - Mailing list pgsql-hackers

<p><font size="2">Simon,<br /><br /> What happens to the data when the function is dropped or replaced?<br /><br /> -
Luke<br/><br /> Msg is shrt cuz m on ma treo<br /><br />  -----Original Message-----<br /> From:   Simon Riggs [<a
href="mailto:simon@2ndquadrant.com">mailto:simon@2ndquadrant.com</a>]<br/> Sent:   Friday, March 09, 2007 06:20 AM
EasternStandard Time<br /> To:     NikhilS<br /> Cc:     Shane Ambler; Luke Lonergan; Zeugswetter Andreas ADI SD; Peter
Eisentraut;pgsql-hackers@postgresql.org<br /> Subject:        Re: [HACKERS] Auto creation of Partitions<br /><br /> On
Fri,2007-03-09 at 11:48 +0530, NikhilS wrote:<br /> > Hi,<br /> ><br /> > On 3/9/07, Shane Ambler
<pgsql@sheeky.biz>wrote:<br /> >        <br /> >         > Note to Nikhil: Make sure the new syntax
doesn'tprevent<br /> >         partitions from<br /> >         > being placed upon multiple tablespaces in
somemanner, at<br /> >         CREATE TABLE<br /> >         > time.<br /> >        <br /> >         What
ifthe syntax was something like -<br /> >        <br /> >         CREATE TABLE tabname (<br /> >             
...<br/> >              ...<br /> >           ) PARTITION BY<br /> >           HASH(expr)<br /> >         |
RANGE(expr)<br/> >         | LIST(expr)<br /> >         [PARTITIONS num_partitions] /* will apply to HASH only
for<br/> >         now*/<br /> >         [PARTITION partition_name CHECK(...) [USING TABLESPACE<br />
>        tblspcname],<br /> >           PARTITION partition_name CHECK(...) [USING TABLESPACE<br /> >        
tblspcname]<br/> >           ...<br /> >         ];<br /> >        <br /> >        <br /> >         And
(ifwe use the ALTER TABLE to add partitions)<br /> >        <br /> >         ALTER TABLE tabname<br />
>        ADD PARTITION partition_name CHECK(...)<br /> >         [USING TABLESPACE tblspcname];<br />
>        <br/> ><br /> ><br /> > We could as well drop the USING part.<br /><br /> Why would we support
HASHpartitions?<br /> If you did, the full syntax for hash clusters should be supported.<br /><br /> If we do the CHECK
clauseslike that then we still have don't have a<br /> guaranteed non-overlap between partitions. It would be easier to
use<br/> Oracle syntax and then construct the CHECK clauses from that.<br /><br /> Also, the syntax needs to be fairly
complexto allow for a mixture of<br /> modes, e.g. range and list partitioning. That is currently possible<br /> today
andthe syntax for doing that is IMHO much simpler than the Oracle<br /> "simple" way of specifying it.<br /><br /> An
alternativeis to provide a partitioning function which decides which<br /> partition each values goes into.<br /><br />
PARTITIONFUNCTION which_partition(date_col)<br /><br /> The partition function must return an unsigned integer > 0,
whichwould<br /> correspond to particular partitions. Partitions would be numbered 1..N,<br /> and named
tablename_partMwhere 1 <= M <= N.<br /><br /> The input and contents of the partition function would be up to
the<br/> user. e.g.<br /><br /> CREATE FUNCTION range_partition(date date_col)<br /> {<br />         if (date_col <
D1)<br/>                 return 1;<br />         else if (date_col < D2)<br />                 return 2;<br />
       else if (date_col < D3)<br />                 return 3;<br /><br />         return 4;<br /> }<br /><br />
Doingit this way would allow us to easily join two tables based upon a<br /> common partition function.<br /><br /> In
time,I would suggest we support both ways: declarative and<br /> functional.<br /><br /> --<br />   Simon
Riggs            <br/>   EnterpriseDB   <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br
/><br/><br /></font>