Automating Partitions in PostgreSQL - Query on syntax

Hi ,

We are working on a
patch<http://archives.postgresql.org/pgsql-hackers/2009-03/msg00897.php>to
automate partitioning in
PostgreSQL.

For Range partitions, we have proposed the syntax which is as follows –

*CREATE TABLE emp (*

* emp_id int not null primary key,*

* designation text not null,*

* location varchar(50) not null,*

* jdate date not null,*

* ctc float not null*

* *

*)*

*PARTITION BY RANGE (emp_id)*

*(*

*emp_500 (START 1 END 500),*

*emp_1500 (START 500 END 1500),*

*emp_4000 (START 1520 END 4000)*

*);*

As observed in this syntax, user needs to specify explicitly, the min and
max values of a range for a given partition.

With this design, partition ranges are inherently allowed to be fragmented
and non-contiguous. As ‘gaps’ are allowed

in the ranges, we’re also supporting an ‘overflow’ partition, so that any
row, which does not satisfy constraints of any

existing partitions, does not stall a big UPDATE operation and such rows are
preserved.(in overflow table)

However, Oracle uses user-friendly syntax but makes it compulsion that
partition ranges *have* to be contiguous.

*PARTITION BY RANGE (emp_id)*

*(*

*Partition emp_500 values less than (500),*

*Partition emp_1500 values less than (1500),*

*Partition emp_4000 values less than (4000),*

*Partition emp_max values less than (maxvalue)*

*);*

As it does not allow fragmented ranges, it automatically removes the need
for an ‘overflow’ partition.

The syntax proposed by us is more flexible and would handle both the cases
of ranges with gaps or ranges without gaps.

I want to seek general opinion from the community on preferences between
user-friendly ‘Oracle’ syntax, and a more generic syntax that allows ‘gaps’
in partition ranges?

Regards,

--

Kedar