- 论坛徽章:
- 0
|
PostgreSQL的分区表是通过INHERIT特性以及，RULE或TRIGGER来实现的。在规则方面，PG推荐使用触发器来实现。
下面是在一个已有的分区表上做扩展分区。
父表：tbl_test
子表：
tbl_test_p0
tbl_test_p1
tbl_test_p10
tbl_test_p11
tbl_test_p12
tbl_test_p13
tbl_test_p14
tbl_test_p15
tbl_test_p16
tbl_test_p17
tbl_test_p18
tbl_test_p19
tbl_test_p2
tbl_test_p20
tbl_test_p21
tbl_test_p22
tbl_test_p23
tbl_test_p24
tbl_test_p25
tbl_test_p26
tbl_test_p27
tbl_test_p_extra
扩展到：
tbl_test_p54
第一步：
创建需要扩展的继承表，赋权限（在9.0以后对父表赋予，查询父表时子表的权限将不再检测），创建索引等
CREATE TABLE tbl_test_p54
(
like tbl_test including defaults,
CONSTRAINT tbl_test_p54_pkey PRIMARY KEY (skyid, currency_id) USING INDEX TABLESPACE tbs_community_idx,
CONSTRAINT tbl_test_p54_skyid_check CHECK (skyid >= 153000000 AND skyid < 154000000)
)
INHERITS (tbl_test)
WITH (
OIDS=FALSE
);
GRANT SELECT ON TABLE tbl_test_p54 TO dwetl;
CREATE INDEX tbl_test_p54_currency_amt_key
ON tbl_test_p54
USING btree
(currency_amt, skyid)
TABLESPACE tbs_community_idx
;
CREATE INDEX tbl_test_p54_currency_id_key
ON tbl_test_p54
USING btree
(currency_id)
TABLESPACE tbs_community_idx
;
CREATE INDEX tbl_test_p54_currency_id_key1
ON tbl_test_p54
USING btree
(currency_id, currency_amt)
TABLESPACE tbs_community_idx
;
CREATE INDEX tbl_test_p54_skyid_key
ON tbl_test_p54
USING btree
(skyid)
TABLESPACE tbs_community_idx
;
第二步：
修改触发器函数
CREATE OR REPLACE FUNCTION tbl_test_delete_trigger()
RETURNS “trigger” AS
$BODY$
BEGIN
IF ( OLD.skyid>=0 and OLD.skyid<100000000 ) THEN
DELETE FROM tbl_test_p0 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=100000000 and OLD.skyid<101000000 ) THEN
DELETE FROM tbl_test_p1 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=101000000 and OLD.skyid<102000000 ) THEN
DELETE FROM tbl_test_p2 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=102000000 and OLD.skyid<103000000 ) THEN
DELETE FROM tbl_test_p3 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=103000000 and OLD.skyid<104000000 ) THEN
DELETE FROM tbl_test_p4 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=104000000 and OLD.skyid<105000000 ) THEN
DELETE FROM tbl_test_p5 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=105000000 and OLD.skyid<106000000 ) THEN
DELETE FROM tbl_test_p6 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=106000000 and OLD.skyid<107000000 ) THEN
DELETE FROM tbl_test_p7 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=107000000 and OLD.skyid<108000000 ) THEN
DELETE FROM tbl_test_p8 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=108000000 and OLD.skyid<109000000 ) THEN
DELETE FROM tbl_test_p9 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=109000000 and OLD.skyid<110000000 ) THEN
DELETE FROM tbl_test_p10 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=110000000 and OLD.skyid<111000000 ) THEN
DELETE FROM tbl_test_p11 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=111000000 and OLD.skyid<112000000 ) THEN
DELETE FROM tbl_test_p12 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=112000000 and OLD.skyid<113000000 ) THEN
DELETE FROM tbl_test_p13 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=113000000 and OLD.skyid<114000000 ) THEN
DELETE FROM tbl_test_p14 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=114000000 and OLD.skyid<115000000 ) THEN
DELETE FROM tbl_test_p15 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=115000000 and OLD.skyid<116000000 ) THEN
DELETE FROM tbl_test_p16 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=116000000 and OLD.skyid<117000000 ) THEN
DELETE FROM tbl_test_p17 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=117000000 and OLD.skyid<118000000 ) THEN
DELETE FROM tbl_test_p18 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=118000000 and OLD.skyid<119000000 ) THEN
DELETE FROM tbl_test_p19 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=119000000 and OLD.skyid<120000000 ) THEN
DELETE FROM tbl_test_p20 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=120000000 and OLD.skyid<121000000 ) THEN
DELETE FROM tbl_test_p21 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=121000000 and OLD.skyid<122000000 ) THEN
DELETE FROM tbl_test_p22 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=122000000 and OLD.skyid<123000000 ) THEN
DELETE FROM tbl_test_p23 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=123000000 and OLD.skyid<124000000 ) THEN
DELETE FROM tbl_test_p24 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=124000000 and OLD.skyid<125000000 ) THEN
DELETE FROM tbl_test_p25 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=125000000 and OLD.skyid<126000000 ) THEN
DELETE FROM tbl_test_p26 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=126000000 and OLD.skyid<127000000 ) THEN
DELETE FROM tbl_test_p27 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=127000000 and OLD.skyid<128000000 ) THEN
DELETE FROM tbl_test_p28 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=128000000 and OLD.skyid<129000000 ) THEN
DELETE FROM tbl_test_p29 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=129000000 and OLD.skyid<130000000 ) THEN
DELETE FROM tbl_test_p30 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=130000000 and OLD.skyid<131000000 ) THEN
DELETE FROM tbl_test_p31 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=131000000 and OLD.skyid<132000000 ) THEN
DELETE FROM tbl_test_p32 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=132000000 and OLD.skyid<133000000 ) THEN
DELETE FROM tbl_test_p33 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=133000000 and OLD.skyid<134000000 ) THEN
DELETE FROM tbl_test_p34 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=134000000 and OLD.skyid<135000000 ) THEN
DELETE FROM tbl_test_p35 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=135000000 and OLD.skyid<136000000 ) THEN
DELETE FROM tbl_test_p36 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=136000000 and OLD.skyid<137000000 ) THEN
DELETE FROM tbl_test_p37 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=137000000 and OLD.skyid<138000000 ) THEN
DELETE FROM tbl_test_p38 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=138000000 and OLD.skyid<139000000 ) THEN
DELETE FROM tbl_test_p39 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=139000000 and OLD.skyid<140000000 ) THEN
DELETE FROM tbl_test_p40 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=140000000 and OLD.skyid<141000000 ) THEN
DELETE FROM tbl_test_p41 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=141000000 and OLD.skyid<142000000 ) THEN
DELETE FROM tbl_test_p42 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=142000000 and OLD.skyid<143000000 ) THEN
DELETE FROM tbl_test_p43 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=143000000 and OLD.skyid<144000000 ) THEN
DELETE FROM tbl_test_p44 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=144000000 and OLD.skyid<145000000 ) THEN
DELETE FROM tbl_test_p45 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=145000000 and OLD.skyid<146000000 ) THEN
DELETE FROM tbl_test_p46 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=146000000 and OLD.skyid<147000000 ) THEN
DELETE FROM tbl_test_p47 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=147000000 and OLD.skyid<148000000 ) THEN
DELETE FROM tbl_test_p48 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=148000000 and OLD.skyid<149000000 ) THEN
DELETE FROM tbl_test_p49 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=149000000 and OLD.skyid<150000000 ) THEN
DELETE FROM tbl_test_p50 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=150000000 and OLD.skyid<151000000 ) THEN
DELETE FROM tbl_test_p51 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=151000000 and OLD.skyid<152000000 ) THEN
DELETE FROM tbl_test_p52 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=152000000 and OLD.skyid<153000000 ) THEN
DELETE FROM tbl_test_p53 where skyid=OLD.skyid;
ELSIF ( OLD.skyid>=153000000 and OLD.skyid<154000000 ) THEN
DELETE FROM tbl_test_p54 where skyid=OLD.skyid;
ELSIF ( OLD.skyid<0 or OLD.skyid>=154000000 ) THEN
DELETE FROM tbl_test_p_extra where skyid=OLD.skyid;
ELSE
RAISE EXCEPTION ’skyid out of range. Fix the tbl_test_delete_trigger() function!’;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
—–
CREATE OR REPLACE FUNCTION tbl_test_insert_trigger()
RETURNS “trigger” AS
$BODY$
BEGIN
IF ( NEW.skyid>=0 and NEW.skyid<100000000 ) THEN
INSERT INTO tbl_test_p0 VALUES (NEW.*);
ELSIF ( NEW.skyid>=100000000 and NEW.skyid<101000000 ) THEN
INSERT INTO tbl_test_p1 VALUES (NEW.*);
ELSIF ( NEW.skyid>=101000000 and NEW.skyid<102000000 ) THEN
INSERT INTO tbl_test_p2 VALUES (NEW.*);
ELSIF ( NEW.skyid>=102000000 and NEW.skyid<103000000 ) THEN
INSERT INTO tbl_test_p3 VALUES (NEW.*);
ELSIF ( NEW.skyid>=103000000 and NEW.skyid<104000000 ) THEN
INSERT INTO tbl_test_p4 VALUES (NEW.*);
ELSIF ( NEW.skyid>=104000000 and NEW.skyid<105000000 ) THEN
INSERT INTO tbl_test_p5 VALUES (NEW.*);
ELSIF ( NEW.skyid>=105000000 and NEW.skyid<106000000 ) THEN
INSERT INTO tbl_test_p6 VALUES (NEW.*);
ELSIF ( NEW.skyid>=106000000 and NEW.skyid<107000000 ) THEN
INSERT INTO tbl_test_p7 VALUES (NEW.*);
ELSIF ( NEW.skyid>=107000000 and NEW.skyid<108000000 ) THEN
INSERT INTO tbl_test_p8 VALUES (NEW.*);
ELSIF ( NEW.skyid>=108000000 and NEW.skyid<109000000 ) THEN
INSERT INTO tbl_test_p9 VALUES (NEW.*);
ELSIF ( NEW.skyid>=109000000 and NEW.skyid<110000000 ) THEN
INSERT INTO tbl_test_p10 VALUES (NEW.*);
ELSIF ( NEW.skyid>=110000000 and NEW.skyid<111000000 ) THEN
INSERT INTO tbl_test_p11 VALUES (NEW.*);
ELSIF ( NEW.skyid>=111000000 and NEW.skyid<112000000 ) THEN
INSERT INTO tbl_test_p12 VALUES (NEW.*);
ELSIF ( NEW.skyid>=112000000 and NEW.skyid<113000000 ) THEN
INSERT INTO tbl_test_p13 VALUES (NEW.*);
ELSIF ( NEW.skyid>=113000000 and NEW.skyid<114000000 ) THEN
INSERT INTO tbl_test_p14 VALUES (NEW.*);
ELSIF ( NEW.skyid>=114000000 and NEW.skyid<115000000 ) THEN
INSERT INTO tbl_test_p15 VALUES (NEW.*);
ELSIF ( NEW.skyid>=115000000 and NEW.skyid<116000000 ) THEN
INSERT INTO tbl_test_p16 VALUES (NEW.*);
ELSIF ( NEW.skyid>=116000000 and NEW.skyid<117000000 ) THEN
INSERT INTO tbl_test_p17 VALUES (NEW.*);
ELSIF ( NEW.skyid>=117000000 and NEW.skyid<118000000 ) THEN
INSERT INTO tbl_test_p18 VALUES (NEW.*);
ELSIF ( NEW.skyid>=118000000 and NEW.skyid<119000000 ) THEN
INSERT INTO tbl_test_p19 VALUES (NEW.*);
ELSIF ( NEW.skyid>=119000000 and NEW.skyid<120000000 ) THEN
INSERT INTO tbl_test_p20 VALUES (NEW.*);
ELSIF ( NEW.skyid>=120000000 and NEW.skyid<121000000 ) THEN
INSERT INTO tbl_test_p21 VALUES (NEW.*);
ELSIF ( NEW.skyid>=121000000 and NEW.skyid<122000000 ) THEN
INSERT INTO tbl_test_p22 VALUES (NEW.*);
ELSIF ( NEW.skyid>=122000000 and NEW.skyid<123000000 ) THEN
INSERT INTO tbl_test_p23 VALUES (NEW.*);
ELSIF ( NEW.skyid>=123000000 and NEW.skyid<124000000 ) THEN
INSERT INTO tbl_test_p24 VALUES (NEW.*);
ELSIF ( NEW.skyid>=124000000 and NEW.skyid<125000000 ) THEN
INSERT INTO tbl_test_p25 VALUES (NEW.*);
ELSIF ( NEW.skyid>=125000000 and NEW.skyid<126000000 ) THEN
INSERT INTO tbl_test_p26 VALUES (NEW.*);
ELSIF ( NEW.skyid>=126000000 and NEW.skyid<127000000 ) THEN
INSERT INTO tbl_test_p27 VALUES (NEW.*);
ELSIF ( NEW.skyid>=127000000 and NEW.skyid<128000000 ) THEN
INSERT INTO tbl_test_p28 VALUES (NEW.*);
ELSIF ( NEW.skyid>=128000000 and NEW.skyid<129000000 ) THEN
INSERT INTO tbl_test_p29 VALUES (NEW.*);
ELSIF ( NEW.skyid>=129000000 and NEW.skyid<130000000 ) THEN
INSERT INTO tbl_test_p30 VALUES (NEW.*);
ELSIF ( NEW.skyid>=130000000 and NEW.skyid<131000000 ) THEN
INSERT INTO tbl_test_p31 VALUES (NEW.*);
ELSIF ( NEW.skyid>=131000000 and NEW.skyid<132000000 ) THEN
INSERT INTO tbl_test_p32 VALUES (NEW.*);
ELSIF ( NEW.skyid>=132000000 and NEW.skyid<133000000 ) THEN
INSERT INTO tbl_test_p33 VALUES (NEW.*);
ELSIF ( NEW.skyid>=133000000 and NEW.skyid<134000000 ) THEN
INSERT INTO tbl_test_p34 VALUES (NEW.*);
ELSIF ( NEW.skyid>=134000000 and NEW.skyid<135000000 ) THEN
INSERT INTO tbl_test_p35 VALUES (NEW.*);
ELSIF ( NEW.skyid>=135000000 and NEW.skyid<136000000 ) THEN
INSERT INTO tbl_test_p36 VALUES (NEW.*);
ELSIF ( NEW.skyid>=136000000 and NEW.skyid<137000000 ) THEN
INSERT INTO tbl_test_p37 VALUES (NEW.*);
ELSIF ( NEW.skyid>=137000000 and NEW.skyid<138000000 ) THEN
INSERT INTO tbl_test_p38 VALUES (NEW.*);
ELSIF ( NEW.skyid>=138000000 and NEW.skyid<139000000 ) THEN
INSERT INTO tbl_test_p39 VALUES (NEW.*);
ELSIF ( NEW.skyid>=139000000 and NEW.skyid<140000000 ) THEN
INSERT INTO tbl_test_p40 VALUES (NEW.*);
ELSIF ( NEW.skyid>=140000000 and NEW.skyid<141000000 ) THEN
INSERT INTO tbl_test_p41 VALUES (NEW.*);
ELSIF ( NEW.skyid>=141000000 and NEW.skyid<142000000 ) THEN
INSERT INTO tbl_test_p42 VALUES (NEW.*);
ELSIF ( NEW.skyid>=142000000 and NEW.skyid<143000000 ) THEN
INSERT INTO tbl_test_p43 VALUES (NEW.*);
ELSIF ( NEW.skyid>=143000000 and NEW.skyid<144000000 ) THEN
INSERT INTO tbl_test_p44 VALUES (NEW.*);
ELSIF ( NEW.skyid>=144000000 and NEW.skyid<145000000 ) THEN
INSERT INTO tbl_test_p45 VALUES (NEW.*);
ELSIF ( NEW.skyid>=145000000 and NEW.skyid<146000000 ) THEN
INSERT INTO tbl_test_p46 VALUES (NEW.*);
ELSIF ( NEW.skyid>=146000000 and NEW.skyid<147000000 ) THEN
INSERT INTO tbl_test_p47 VALUES (NEW.*);
ELSIF ( NEW.skyid>=147000000 and NEW.skyid<148000000 ) THEN
INSERT INTO tbl_test_p48 VALUES (NEW.*);
ELSIF ( NEW.skyid>=148000000 and NEW.skyid<149000000 ) THEN
INSERT INTO tbl_test_p49 VALUES (NEW.*);
ELSIF ( NEW.skyid>=149000000 and NEW.skyid<150000000 ) THEN
INSERT INTO tbl_test_p50 VALUES (NEW.*);
ELSIF ( NEW.skyid>=150000000 and NEW.skyid<151000000 ) THEN
INSERT INTO tbl_test_p51 VALUES (NEW.*);
ELSIF ( NEW.skyid>=151000000 and NEW.skyid<152000000 ) THEN
INSERT INTO tbl_test_p52 VALUES (NEW.*);
ELSIF ( NEW.skyid>=152000000 and NEW.skyid<153000000 ) THEN
INSERT INTO tbl_test_p53 VALUES (NEW.*);
ELSIF ( NEW.skyid>=153000000 and NEW.skyid<154000000 ) THEN
INSERT INTO tbl_test_p54 VALUES (NEW.*);
ELSIF ( NEW.skyid<0 or NEW.skyid>=154000000 ) THEN
INSERT INTO tbl_test_p_extra VALUES (NEW.*);
ELSE
RAISE EXCEPTION ’skyid out of range. Fix the tbl_test_insert_trigger() function!’;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
第三步：
修改默认表的约束
alter table tbl_test_p_extra drop constraint tbl_test_p_extra_skyid_check;
alter table tbl_test_p_extra add constraint tbl_test_p_extra_skyid_check CHECK (skyid < 0 OR skyid >= 154000000);
提醒：
要让执行计划器使用分区约束，必须满足一下条件：
1.非变量
传入到分区条件的字段的匹配不能使用变量，否则的话就不会用到分区作为限制查询段。
2.参数
constraint_exclusion = on |
|
免费注册
发表于 2011-02-22 20:06
