投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

年底了， 给分区表扩分区

• 数据库 • 阅读 52

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

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

创建父表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
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)  
);

创建子表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
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);

创建数据删除触发器函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
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$

创建数据插入触发器函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
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$

创建数据插入、删除触发器

1
2
 
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

1
2
3
4
 
create sequence fenqu_table_id_seq  
INCREMENT by 1  
MINVALUE 1  
start with 1;

分区表测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
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)

原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/tech/database/236334.html

赞 (0)
0 0
上一篇 2022年1月24日 21:26
下一篇 2022年1月24日 21:26

相关推荐

发表回复

登录后才能评论