Re: On partitioning

On Wed, Dec 10, 2014 at 9:22 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> The problem with naming partitions is that the user has to pick names
> for every partition, which is tedious and doesn't provide any
> significant benefit. The input I had from users of other partitioning
> systems was that they very much preferred not to name the partitions at
> all, which is why I chose the PARTITION FOR VALUE syntax (not sure if
> this syntax is exactly what other systems use; it just seemed the
> natural choice.)

FWIW, Oracle does name partitions. It generates the names
automatically if you don't care to specify them, and the partition
names for a given table live in their own namespace that is separate
from the toplevel object namespace. For example:

CREATE TABLE sales
( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
STORAGE (INITIAL 100K NEXT 50K) LOGGING
PARTITION BY RANGE ( sale_year, sale_month, sale_day)
( PARTITION sales_q1 VALUES LESS THAN ( 1999, 04, 01 )
TABLESPACE tsa STORAGE (INITIAL 20K, NEXT 10K),
PARTITION sales_q2 VALUES LESS THAN ( 1999, 07, 01 )
TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN ( 1999, 10, 01 )
TABLESPACE tsc,
PARTITION sales q4 VALUES LESS THAN ( 2000, 01, 01 )
TABLESPACE tsd)
ENABLE ROW MOVEMENT;

I don't think this practice has much to recommend it. We're going to
need a way to refer to individual partitions by name, and I don't see
much benefit in making that name something other than what is stored
in pg_class.relname.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company