在使用greenplum中会使用分区表，但同时分区表需要维护分区；比如加分区，这个过程比较痛苦，查询相关资料以后有了相应的解决办法，但是该办法也不是万能的，有诸多限制，关于限制有兴趣的同学可以查看我文章最后提到的参考资料。
创建2个表（range分区表和list分区表）
CREATE TABLE tb1_partition_list_yyyymmdd (
id numeric,
yyyymmdd character varying(128)
)
WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (id) PARTITION BY LIST(yyyymmdd)
(
PARTITION p20120811 VALUES('') WITH (tablename='tb1_partition_list_yyyymmdd_1_prt_p20120811', orientation=row , appendonly=true, compresslevel=5 ),
PARTITION p20120812 VALUES('') WITH (tablename='tb1_partition_list_yyyymmdd_1_prt_p20120812', orientation=row , appendonly=true, compresslevel=5 )
);
CREATE TABLE tb1_partition_range_yyyymmdd (
id numeric,
yyyymmdd date
)
WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (id) PARTITION BY RANGE(yyyymmdd)
(
PARTITION p20120811 START ('2012-08-11'::date) END ('2012-08-12'::date) WITH (tablename='tb1_partition_range_yyyymmdd_1_prt_p20120811', orientation=row , appendonly=true, compresslevel=5 ),
PARTITION p20120812 START ('2012-08-12'::date) END ('2012-08-13'::date) WITH (tablename='tb1_partition_range_yyyymmdd_1_prt_p20120812', orientation=row , appendonly=true, compresslevel=5 )
);
上面分别创建了list分区表和range分区表。
下面创建一个辅助视图：
CREATE VIEW v_pg_add_partitions AS
SELECT pp.parrelid AS tableoid, prl.parchildrelid, prl.parname AS partitionname, CASE WHEN (pp.parkind = 'h'::"char") THEN 'hash'::text WHEN (pp.parkind = 'r'::"char") THEN 'range'::text WHEN (pp.parkind = '
l'::"char") THEN 'list'::text ELSE NULL::text END AS partitiontype, translate(pg_get_expr(prl.parlistvalues, prl.parchildrelid), '-'':date character varying bpchar numeric double precision timestamp without time
zone'::text, ''::text) AS partitionlistvalue, "substring"(translate(pg_get_expr(prl.parrangestart, prl.parchildrelid), '-'':date character varying bpchar numeric double precision timestamp without time zone'::t
ext, ''::text), 1, 8) AS partitionrangestart, "substring"(translate(pg_get_expr(prl.parrangeend, prl.parchildrelid), '-'':date character varying bpchar numeric double precision timestamp without time zone'::text
, ''::text), 1, 8) AS partitionrangeend, prl.parruleord AS partitionposition, (("substring"(prl.parlistvalues, 'consttype ([0-9]+)'::text))::integer)::regtype AS listtype, (("substring"(prl.parrangeend, 'constty
pe ([0-9]+)'::text))::integer)::regtype AS rangetype FROM pg_partition pp, pg_partition_rule prl WHERE ((pp.paristemplate = false) AND (prl.paroid = pp.oid));
接下来创建生成添加分区表的命令的函数：
create or replace function add_partition_info(tableoid oid,days_from_now integer)
returns setof text
as $$
import datetime
def now():
d=datetime.datetime.now()
format='%Y%m%d'
return datetime.datetime.strftime(d,format) def add_day(d,n):
format='%Y%m%d'
d2=datetime.datetime.strptime(d,format)
d3=d2+datetime.timedelta(days=n)
return datetime.datetime.strftime(d3,format) def add_month(d,n):
format='%Y%m%d'
formatymd='%Y%m01'
if d.__len__() == 6:
format='%Y%m'
formatymd='%Y%m'
d2=datetime.datetime.strptime(d,format)
d3=d2+datetime.timedelta(days=31*n)
return datetime.datetime.strftime(d3,formatymd) relist=[]
sql=""" select * from (select *,tableoid::regclass tablename,lead(case when partitionrangeend <> '' then partitionrangeend else partitionlistvalue end) over (partition by tableoid order by partitionposition desc) as pre_value,row_number() over (partition by tableoid order by partitionposition desc) rn from v_pg_add_partitions where substr(partitionname,1,3)='p20' and tableoid=%s) t where rn=1;""" % (tableoid);
rv=plpy.execute(sql);
sql_relation="select array_to_string(reloptions,',') reloptions from pg_class where oid=%s" % (tableoid)
rv_relation=plpy.execute(sql_relation) if rv.nrows()!=1:
return None
else:
reloptions = rv_relation[]['reloptions']
tablename=rv[]['tablename']
partitiontype=rv[]['partitiontype']
partitionname=rv[]['partitionname']
pre_value=rv[]['pre_value']
now_add_7days=add_day(now(),days_from_now) if partitiontype=='range':
rangetype=rv[]['rangetype']
partitionrangestart=rv[]['partitionrangestart']
partitionrangeend=rv[]['partitionrangeend']
interval = int(partitionrangeend) - int(pre_value) if partitionname.__len__()==7:
func_add=add_month
interval=int(partitionrangeend[0:6]) - int(pre_value[0:6]) elif partitionname.__len__()==9:
func_add=add_day else:
return None
while partitionrangestart < now_add_7days:
partitionrangestart = func_add(partitionrangestart,interval)
partitionrangeend = func_add(partitionrangeend,interval)
partitionname="p"+func_add(partitionname[1:],interval)
add_sql = "alter table %s add partition %s start ('%s'::%s) end ('%s'::%s) " % (tablename,partitionname,partitionrangestart,rangetype,partitionrangeend,rangetype)
if reloptions != None and reloptions != '':
add_sql+='with (%s);' % (reloptions)
else:
add_sql+=";"
relist.append(add_sql) if partitiontype=='list':
listtype=rv[]['listtype']
partitionlistvalue=rv[]['partitionlistvalue']
interval=int(partitionlistvalue) - int(pre_value) if partitionname.__len__()==7:
func_add = add_month elif partitionname.__len__()==9:
func_add=add_day else:
return None
while partitionlistvalue < now_add_7days:
partitionlistvalue=func_add(partitionlistvalue,interval)
partitionname="p"+func_add(partitionname[1:],interval)
add_sql="alter table %s add partition %s values ('%s'::%s)" % (tablename,partitionname,partitionlistvalue,listtype)
if reloptions != None and reloptions != '':
add_sql +='with (%s);' % (reloptions)
else:
add_sql+=";"
relist.append(add_sql)
return relist
$$ LANGUAGE plpythonu;
最后我们进行测试（增加当前时间3天内的分区）
test_db=# SELECT add_partition_info('tb1_partition_range_yyyymmdd'::regclass,3);
add_partition_info
----------------------------------------------------------------------------------------------------------------------------------------------------------
alter table tb1_partition_range_yyyymmdd add partition p20120813 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120814 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120815 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120816 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120817 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120818 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120819 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120820 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120821 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120822 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120823 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120824 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120825 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120826 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120827 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120828 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120829 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120830 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120831 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120901 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120902 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120903 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120904 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120905 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120906 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120907 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120908 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
alter table tb1_partition_range_yyyymmdd add partition p20120909 start (''::date) end (''::date) with (appendonly=true,compresslevel=5);
再创建一个range分区且是按月分区的表
CREATE TABLE tb1(
id numeric,
yyyymmdd date
)DISTRIBUTED BY (id) PARTITION BY RANGE(yyyymmdd)
(
PARTITION p201208 START ('2012-08-01'::date) END ('2012-09-01'::date) ,
PARTITION p201209 START ('2012-09-01'::date) END ('2012-10-01'::date)
);
test_db=# SELECT add_partition_info('tb1'::regclass,1);
add_partition_info
-----------------------------------------------------------------------------------------
alter table tb1 add partition p201210 start (''::date) end (''::date) ;
alter table tb1 add partition p201211 start (''::date) end (''::date) ;
alter table tb1 add partition p201212 start (''::date) end (''::date) ;
alter table tb1 add partition p201301 start (''::date) end (''::date) ;
alter table tb1 add partition p201302 start (''::date) end (''::date) ;
alter table tb1 add partition p201303 start (''::date) end (''::date) ;
alter table tb1 add partition p201304 start (''::date) end (''::date) ;
alter table tb1 add partition p201305 start (''::date) end (''::date) ;
alter table tb1 add partition p201306 start (''::date) end (''::date) ;
alter table tb1 add partition p201307 start (''::date) end (''::date) ;
alter table tb1 add partition p201308 start (''::date) end (''::date) ;
alter table tb1 add partition p201309 start (''::date) end (''::date) ;
alter table tb1 add partition p201310 start (''::date) end (''::date) ;
alter table tb1 add partition p201311 start (''::date) end (''::date) ;
alter table tb1 add partition p201312 start (''::date) end (''::date) ;
alter table tb1 add partition p201401 start (''::date) end (''::date) ;
alter table tb1 add partition p201402 start (''::date) end (''::date) ;
alter table tb1 add partition p201403 start (''::date) end (''::date) ;
alter table tb1 add partition p201404 start (''::date) end (''::date) ;
alter table tb1 add partition p201405 start (''::date) end (''::date) ;
alter table tb1 add partition p201406 start (''::date) end (''::date) ;
alter table tb1 add partition p201407 start (''::date) end (''::date) ;
alter table tb1 add partition p201408 start (''::date) end (''::date) ;
alter table tb1 add partition p201409 start (''::date) end (''::date) ;
alter table tb1 add partition p201410 start (''::date) end (''::date) ;
alter table tb1 add partition p201411 start (''::date) end (''::date) ;
alter table tb1 add partition p201412 start (''::date) end (''::date) ;
可以看见也是没有问题的。
参考资料：
《greenplum企业应用实战》