Auto creation of Partitions - Mailing list pgsql-hackers

Hi, <br /><br />This is to get feedback to meet the following TODO:<br /><ul><li>Simplify ability to create partitioned
tables<p> This would allow creation of partitioned tables without requiring creation of rules for INSERT/UPDATE/DELETE,
andconstraints for rapid partition selection. Options could include range and hash partition selection. </ul><br
/>Therewas some discussion on the pgsql mailing lists, which lead to the above TODO: <br /><a
href="http://archives.postgresql.org/pgsql-hackers/2006-09/msg00189.php"
target="_blank">http://archives.postgresql.org/pgsql-hackers/2006-09/msg00189.php</a><br/><a
href="http://archives.postgresql.org/pgsql-hackers/2006-08/msg01874.php"
target="_blank">http://archives.postgresql.org/pgsql-hackers/2006-08/msg01874.php</a><br/><br />We can have the
followingsyntax to support auto creation of partitions in Postgresql:<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 now*/<br /> [PARTITION partition_name CHECK(...),<br />  PARTITION
partition_nameCHECK(...) <br />  ...<br /> ];<br /><br />Here "expr" will be one of the column names as specified for
themaster table. Once we finalize the syntax, the above statement would end up carrying out the following activities
(disclaimer:we might add or remove some activities based on the discussion here). <br /><br /> i ) Create master
table.<br/>ii) Create children tables based on the number of partitions specified and make them inherit from the master
table.<br/>iii) Auto generate rules (or triggers?) using the checks mentioned for the partitions, to handle
INSERTs/DELETEs/UPDATEsto navigate them to the appropriate child. Note that checks specified directly on the master
tablewill get inherited automatically. <br />iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass
iton to the children tables.<br />v) If possible add CHECK (false) to the master table to avoid any activity on it. <br
/><br/> Some questions remain as to: <br /><br />1) Whether we should use triggers/rules for step number (iii) above.
Mayberules is the way to go.<br />2) What other attributes (access permissions e.g.) of the master along with the ones
specifiedin (iv) should be passed on to the children. <br />3) Some implementation specific issue e.g. whether
SPI_executewould be a good way of creating these rules.<br /><br />Comments appreciated, <br />Regards,<br />Nikhils<br
/>EnterpriseDB              <a href="http://www.enterprisedb.com" target="_blank">http://www.enterprisedb.com</a>