注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

年底了, 给分区表扩分区  

2010-10-29 10:04:18|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

    

        年底了,这边Postgresql的很多库都建了分区表,需要扩分区,这次计划将所有PG库的所有分区表的
分扩扩到201207,扩分区是一件比较琐碎而又重要的事情, 琐碎是因为扩分区需要编辑大量脚本,重要是
因为如果一个分区月份搞错,或者是权限未加,那么这张表很有可能写不进数据,也不能访问,所以在整个
扩分区的过程中需要仔细检查每一个脚本,核对月份,月份范围,以前触发器脚本等。

       这边有很多分区表,扩分区比较费时间,有空的时候才愿意去做,在年底前,计划每天扩个两张分区表
一直到扩完为止。

       Postgresql的分区表创建较Oracle分区表复杂些,需要借助创建继承表和触发器的模式实现, 具体创建
方法可以参照以下实验, 供参考。  

  --创建父表
CREATE TABLE fenqu_table (
    id integer NOT NULL,
    mcc character varying(5),
    lac character varying(8),
    mnc character(2),
    cellid character varying(8),
    signal integer,
    antennaheight integer,
    cellpower integer,
    addtime timestamp(0) without time zone DEFAULT now(),
    cellidfrom character varying(200),
    address character varying(200),
    mark integer DEFAULT 100,
    fail_times integer DEFAULT 0,
    success_times integer DEFAULT 0,
    skyid integer DEFAULT 0,
    mapaddress character varying(200)
);

--创建子表
CREATE TABLE fenqu_table_p200905 (
    id integer,
    mcc character varying(5),
    lac character varying(8),
    mnc character(2),
    cellid character varying(8),
    signal integer,
    antennaheight integer,
    cellpower integer,
    addtime timestamp(0) without time zone,
    cellidfrom character varying(200),
    address character varying(200),
    mark integer,
    fail_times integer,
    success_times integer,
    skyid integer,
    mapaddress character varying(200),
    CONSTRAINT fenqu_table_p200905_addtime_check CHECK (((addtime >= '2009-05-01 00:00:00'::timestamp without time zone) AND (addtime < '2009-06-01 00:00:00'::timestamp without time zone)))
)
INHERITS (fenqu_table);

CREATE TABLE fenqu_table_p200906 (
    id integer,
    mcc character varying(5),
    lac character varying(8),
    mnc character(2),
    cellid character varying(8),
    signal integer,
    antennaheight integer,
    cellpower integer,
    addtime timestamp(0) without time zone,
    cellidfrom character varying(200),
    address character varying(200),
    mark integer,
    fail_times integer,
    success_times integer,
    skyid integer,
    mapaddress character varying(200),
    CONSTRAINT fenqu_table_p200906_addtime_check CHECK (((addtime >= '2009-06-01 00:00:00'::timestamp without time zone) AND (addtime < '2009-07-01 00:00:00'::timestamp without time zone)))
)
INHERITS (fenqu_table);

CREATE TABLE fenqu_table_p200907 (
    id integer,
    mcc character varying(5),
    lac character varying(8),
    mnc character(2),
    cellid character varying(8),
    signal integer,
    antennaheight integer,
    cellpower integer,
    addtime timestamp(0) without time zone,
    cellidfrom character varying(200),
    address character varying(200),
    mark integer,
    fail_times integer,
    success_times integer,
    skyid integer,
    mapaddress character varying(200),
    CONSTRAINT fenqu_table_p200907_addtime_check CHECK (((addtime >= '2009-07-01 00:00:00'::timestamp without time zone) AND (addtime < '2009-08-01 00:00:00'::timestamp without time zone)))
)
INHERITS (fenqu_table);
 
 
--创建 数据删除触发器函数
 CREATE OR REPLACE FUNCTION public.fenqu_table_delete_trigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF    ( OLD.addtime>='2009-05-01' and OLD.addtime<'2009-06-01' ) THEN
        DELETE FROM fenqu_table_p200905 where cretime=OLD.cretime;
    ELSIF ( OLD.addtime>='2009-06-01' and OLD.addtime<'2009-07-01' ) THEN
        DELETE FROM fenqu_table_p200906 where cretime=OLD.cretime;
    ELSIF ( OLD.addtime>='2009-07-01' and OLD.addtime<'2009-08-01' ) THEN
        DELETE FROM fenqu_table_p200907 where cretime=OLD.cretime;
    ELSE
        RAISE EXCEPTION 'addtime out of range.  Fix the fenqu_table_delete_trigger() function!';
    END IF;
    RETURN NULL;
END;
$function$

--创建 数据插入触发器函数
CREATE OR REPLACE FUNCTION public.fenqu_table_insert_trigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF    ( NEW.addtime>='2009-05-01' and NEW.addtime<'2009-06-01' ) THEN
        INSERT INTO fenqu_table_p200905 VALUES (NEW.*);
    ELSIF ( NEW.addtime>='2009-06-01' and NEW.addtime<'2009-07-01' ) THEN
        INSERT INTO fenqu_table_p200906 VALUES (NEW.*);
    ELSIF ( NEW.addtime>='2009-07-01' and NEW.addtime<'2009-08-01' ) THEN
        INSERT INTO fenqu_table_p200907 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'addtime out of range.  Fix the fenqu_table_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$function$

--创建数据插入,删除触发器
create trigger delete_fenqu_table_trigger BEFORE DELETE ON fenqu_table FOR EACH ROW EXECUTE PROCEDURE fenqu_table_delete_trigger();
create trigger insert_fenqu_table_trigger BEFORE INSERT ON fenqu_table FOR EACH ROW EXECUTE PROCEDURE fenqu_table_insert_trigger();
 
--create sequence
create sequence fenqu_table_id_seq
INCREMENT by  1
MINVALUE 1
start with 1;
 
--测试
mydb=> insert into fenqu_table (id,addtime) values (nextval('fenqu_table_id_seq'),'2009-05-01 00:00:00');
INSERT 0 0

mydb=> select count(*) from fenqu_table;
 count
-------
     1
(1 row)

mydb=> select count(*) from only fenqu_table;
 count
-------
     0
(1 row)

mydb=> select count(*) from  fenqu_table_p200905;
 count
-------
     1
(1 row)

  评论这张
 
阅读(30594)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016