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