Automating Partitions in PostgreSQL - Query on syntax - Mailing list pgsql-hackers

<p class="MsoNormal">Hi ,<p class="MsoNormal"> <p class="MsoNormal">We are working on a <a
href="http://archives.postgresql.org/pgsql-hackers/2009-03/msg00897.php">patch</a>to automate partitioning<span
style="color:navy;"> </span>in PostgreSQL.<span style="font-size: 10pt; font-family: "Arial","sans-serif"; color:
navy;"><br/></span><p class="MsoNormal"> <p class="MsoNormal">For Range partitions, we have proposed the syntax which
isas follows –<p class="MsoNormal"> <p class="MsoNormal" style=""><b><span style="font-size: 10pt; font-family:
"CourierNew";">CREATE TABLE emp (</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt; font-family:
"CourierNew";">    emp_id          int not null primary key,</span></b><p class="MsoNormal" style=""><b><span
style="font-size:10pt; font-family: "Courier New";">    designation     text not null,</span></b><p class="MsoNormal"
style=""><b><spanstyle="font-size: 10pt; font-family: "Courier New";">    location        varchar(50) not
null,</span></b><pclass="MsoNormal" style=""><b><span style="font-size: 10pt; font-family: "Courier New";">   
jdate          date not null,</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt; font-family:
"CourierNew";">    ctc             float not null</span></b><p class="MsoNormal" style=""><b><span style="font-size:
10pt;font-family: "Courier New";"> </span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt;
font-family:"Courier New";">)</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt; font-family:
"CourierNew";">PARTITION BY RANGE (emp_id)</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt;
font-family:"Courier New";">(</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt; font-family:
"CourierNew";">emp_500 (START 1 END 500),</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt;
font-family:"Courier New";">emp_1500 (START 500 END 1500),</span></b><p class="MsoNormal" style=""><b><span
style="font-size:10pt; font-family: "Courier New";">emp_4000 (START 1520 END 4000)</span></b><p
class="MsoNormal"><b><spanstyle="font-size: 10pt; font-family: "Courier New";">);</span></b><p class="MsoNormal"> <p
class="MsoNormal">Asobserved in this syntax, user needs to specify explicitly, the min and max values of a range for a
givenpartition. <p class="MsoNormal">With this design, partition ranges are inherently allowed to be fragmented and
non-contiguous.As ‘gaps’ are allowed <p class="MsoNormal">in the ranges, we’re also supporting an ‘overflow’ partition,
sothat any row, which does not satisfy constraints of any <p class="MsoNormal">existing partitions, does not stall a
bigUPDATE operation and such rows are preserved.(in overflow table)<p class="MsoNormal"> <p class="MsoNormal">However,
Oracleuses user-friendly syntax but make<span style="color: navy;">s</span> it compulsion that partition ranges *have*
tobe contiguous. <p class="MsoNormal"> <p class="MsoNormal" style=""><b><span style="font-size: 10pt; font-family:
"CourierNew";">PARTITION BY RANGE (emp_id)</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt;
font-family:"Courier New";">(</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt; font-family:
"CourierNew";">Partition emp_500  values less than (500),</span></b><p class="MsoNormal" style=""><b><span
style="font-size:10pt; font-family: "Courier New";">Partition emp_1500 values less than (1500),</span></b><p
class="MsoNormal"style=""><b><span style="font-size: 10pt; font-family: "Courier New";">Partition emp_4000 values less
than(4000),</span></b><p class="MsoNormal" style=""><b><span style="font-size: 10pt; font-family: "Courier
New";">Partitionemp_max  values less than (maxvalue)</span></b><p class="MsoNormal"><b><span style="font-size: 10pt;
font-family:"Courier New";">);</span></b><p class="MsoNormal"> <p class="MsoNormal">As it does not allow fragmented
ranges,it automatically removes the need for an ‘overflow’ partition. <span style="color: navy;"> </span><p
class="MsoNormal"> <pclass="MsoNormal">The syntax proposed by us is more flexible and would handle both the cases of
rangeswith gaps or ranges without gaps. <p class="MsoNormal"> <p class="MsoNormal">I want to seek general opinion from
thecommunity on preferences between user-friendly ‘Oracle’ syntax, and a more generic syntax that allows ‘gaps’ in
partitionranges?<span style="font-size: 10pt; font-family: "Arial","sans-serif"; color: navy;"><br /></span><p
class="MsoNormal"> <pclass="MsoNormal">Regards,<p class="MsoNormal">--<p class="MsoNormal">Kedar<br />