pg_partman是基于PostgreSQL分區(qū)開(kāi)發(fā)的一個(gè)分區(qū)表管理工具，通過(guò)多個(gè)引入函數(shù)實(shí)現(xiàn)了對(duì)分區(qū)表的管理，相比手工創(chuàng)建分區(qū)表、觸發(fā)器函數(shù)、觸發(fā)器顯得更加快捷方便，同時(shí)提供了對(duì)分區(qū)表的日常維護(hù)與管理功能。
創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),宜賓企業(yè)網(wǎng)站建設(shè),宜賓品牌網(wǎng)站建設(shè),網(wǎng)站定制,宜賓網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷(xiāo),網(wǎng)絡(luò)優(yōu)化,宜賓網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力。可充分滿(mǎn)足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專(zhuān)業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶(hù)成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。
其實(shí)現(xiàn)原理是將約束、函數(shù)、觸發(fā)器、分區(qū)表的創(chuàng)建以及基礎(chǔ)管理命令均寫(xiě)入函數(shù)，通過(guò)函數(shù)的調(diào)用即可方便創(chuàng)建與維護(hù)，并且避免了手工創(chuàng)建引入錯(cuò)誤。
下載地址： https://github.com/keithf4/pg_partman
編譯安裝：
[postgres@localhost ~]$ cd pg_partman-master [postgres@localhost pg_partman-master]$ make [postgres@localhost pg_partman-master]$ make install
創(chuàng)建partman使用的空間：
postgres=# create schema partman; CREATE SCHEMA
引入擴(kuò)展模塊pg_partman：
postgres=# create extension pg_partman with schema partman ; CREATE EXTENSION postgres=# \dx List of installed extensions Name | Version | Schema | Description ------------+---------+------------+------------------------------------------------------ pg_partman | 1.5.1 | partman | Extension to manage partitioned tables by time or ID plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)
查看自動(dòng)生成的對(duì)象：
重慶分公司
重慶分公司
postgres=# set search_path to partman;
SET
postgres=# \d
List of relations
Schema | Name | Type | Owner
---------+-------------+-------+----------
partman | part_config | table | postgres
(1 row)
{擴(kuò)展模塊pg_partman引入后在partman模式中生成一個(gè)配置記錄表part_config}postgres=# \d part_config Table "partman.part_config" Column | Type | Modifiers ----------------------+---------+------------------------ parent_table | text | not null type | text | not null part_interval | text | not null control | text | not null constraint_cols | text[] | premake | integer | not null default 4 retention | text | retention_schema | text | retention_keep_table | boolean | not null default true retention_keep_index | boolean | not null default true datetime_string | text | last_partition | text | undo_in_progress | boolean | not null default false Indexes: "part_config_parent_table_pkey" PRIMARY KEY, btree (parent_table) "part_config_type_idx" btree (type) Check constraints: "part_config_type_check" CHECK (check_partition_type(type)) "positive_premake_check" CHECK (premake > 0)
新建一個(gè)用于測(cè)試的schema：
postgres=# create schema test; CREATE SCHEMA
創(chuàng)建主表：
postgres=# create table test.part_test(col1 serial, col2 text, col3 timestamptz DEFAULT now() NOT NUll);
CREATE TABLE
postgres=# \d test.part_test
Table "test.part_test"
Column | Type | Modifiers
--------+--------------------------+---------------------------------------------------------------
col1 | integer | not null default nextval('test.part_test_col1_seq'::regclass)
col2 | text |
col3 | timestamp with time zone | not null default now()調(diào)用pg_partman提供的create_parent函數(shù)生成分區(qū)表以及約束、觸發(fā)器函數(shù)和觸發(fā)器：
postgres=# select partman.create_parent('test.part_test', 'col3', 'time-static', 'half-hour');
create_parent
---------------
(1 row)
postgres=# \d+ test.part_test
Table "test.part_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------+---------------------------------------------------------------+----------+--------------+-------------
col1 | integer | not null default nextval('test.part_test_col1_seq'::regclass) | plain | |
col2 | text | | extended | |
col3 | timestamp with time zone | not null default now() | plain | |
Triggers:
part_test_part_trig BEFORE INSERT ON test.part_test FOR EACH ROW EXECUTE PROCEDURE test.part_test_part_trig_func()
Child tables: test.part_test_p2014_02_21_0330,
test.part_test_p2014_02_21_0400,
test.part_test_p2014_02_21_0430,
test.part_test_p2014_02_21_0500,
test.part_test_p2014_02_21_0530,
test.part_test_p2014_02_21_0600,
test.part_test_p2014_02_21_0630,
test.part_test_p2014_02_21_0700,
test.part_test_p2014_02_21_0730
Has OIDs: no
{在主表上創(chuàng)建了trigger并建立了繼承關(guān)系}
postgres=# select now();
now
-------------------------------
2014-02-21 05:37:35.764547+08
(1 row)
postgres=# \d+ test.part_test_p2014_02_21_0330
Table "test.part_test_p2014_02_21_0330"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------+---------------------------------------------------------------+----------+--------------+-------------
col1 | integer | not null default nextval('test.part_test_col1_seq'::regclass) | plain | |
col2 | text | | extended | |
col3 | timestamp with time zone | not null default now() | plain | |
Check constraints:
"part_test_p2014_02_21_0330_partition_check" CHECK (col3 >= '2014-02-21 03:30:00+08'::timestamp with time zone AND col3 < '2014-02-21 04:00:00+08'::timestamp with time zone)
Inherits: test.part_test
Has OIDs: no
{在分區(qū)表上創(chuàng)建了check約束}創(chuàng)建了觸發(fā)器函數(shù)：
postgres=# \df List of functions Schema | Name | Result data type | Argument data types | Type --------+--------------------------+------------------+---------------------+--------- test | part_test_part_trig_func | trigger | | trigger (1 row) postgres=# select prosrc from pg_proc where proname='part_test_part_trig_func'; prosrc ---------------------------------------------------------------------------------------------------------- + BEGIN + IF TG_OP = 'INSERT' THEN + IF NEW.col3 >= '2014-02-21 05:30:00+08' AND NEW.col3 < '2014-02-21 06:00:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0530 VALUES (NEW.*); + ELSIF NEW.col3 >= '2014-02-21 05:00:00+08' AND NEW.col3 < '2014-02-21 05:30:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0500 VALUES (NEW.*); + ELSIF NEW.col3 >= '2014-02-21 06:00:00+08' AND NEW.col3 < '2014-02-21 06:30:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0600 VALUES (NEW.*); + ELSIF NEW.col3 >= '2014-02-21 04:30:00+08' AND NEW.col3 < '2014-02-21 05:00:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0430 VALUES (NEW.*); + ELSIF NEW.col3 >= '2014-02-21 06:30:00+08' AND NEW.col3 < '2014-02-21 07:00:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0630 VALUES (NEW.*); + ELSIF NEW.col3 >= '2014-02-21 04:00:00+08' AND NEW.col3 < '2014-02-21 04:30:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0400 VALUES (NEW.*); + ELSIF NEW.col3 >= '2014-02-21 07:00:00+08' AND NEW.col3 < '2014-02-21 07:30:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0700 VALUES (NEW.*); + ELSIF NEW.col3 >= '2014-02-21 03:30:00+08' AND NEW.col3 < '2014-02-21 04:00:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0330 VALUES (NEW.*); + ELSIF NEW.col3 >= '2014-02-21 07:30:00+08' AND NEW.col3 < '2014-02-21 08:00:00+08' THEN + INSERT INTO test.part_test_p2014_02_21_0730 VALUES (NEW.*); + ELSE + RETURN NEW; + END IF; + END IF; + RETURN NULL; + END (1 row)
并在配置表part_config中添加一條記錄：
postgres=# \x Expanded display is on. postgres=# select * from partman.part_config ; -[ RECORD 1 ]--------+-------------------------------- parent_table | test.part_test type | time-static part_interval | 00:30:00 control | col3 constraint_cols | premake | 4 retention | retention_schema | retention_keep_table | t retention_keep_index | t datetime_string | YYYY_MM_DD_HH24MI last_partition | test.part_test_p2014_02_21_0730 undo_in_progress | f
[
Parent_table:主表名稱(chēng)
Type:分區(qū)類(lèi)型，包括time-static/time-dynamic/id-static/id-dynamic四種類(lèi)型
Part_interval:分區(qū)間隔
Control:鍵字字段
Constraint_cols:
Premake:生成分區(qū)表時(shí)分別向當(dāng)前時(shí)間段分區(qū)的前后各再生成的分區(qū)表個(gè)數(shù)
Retention:
Retention_schema:
Retention_keep_table:是否在刪除分區(qū)表時(shí)只取消繼承關(guān)系
Retention_keep_index:未繼承的分區(qū)表的索引是否被刪除
Datetime_string:時(shí)間格式
Last_partition:最后的分區(qū)表
Undo_in_progress:
]
插入測(cè)試數(shù)據(jù)：
[root@localhost ~]# date -s 03:45:00
Fri Feb 21 03:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian1');
INSERT 0 0
[root@localhost ~]# date -s 04:15:00
Fri Feb 21 04:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian2');
INSERT 0 0
[root@localhost ~]# date -s 04:45:00
Fri Feb 21 04:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian3');
INSERT 0 0
[root@localhost ~]# date -s 05:15:00
Fri Feb 21 05:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian4');
INSERT 0 0
[root@localhost ~]# date -s 05:45:00
Fri Feb 21 05:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian5');
INSERT 0 0
[root@localhost ~]# date -s 06:15:00
Fri Feb 21 06:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian6');
INSERT 0 0
[root@localhost ~]# date -s 06:45:00
Fri Feb 21 06:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian7');
INSERT 0 0
[root@localhost ~]# date -s 07:15:00
Fri Feb 21 07:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian8');
INSERT 0 0
[root@localhost ~]# date -s 07:45:00
Fri Feb 21 07:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian9');
INSERT 0 0
[root@localhost ~]# date -s 08:15:00
Fri Feb 21 08:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian10');
INSERT 0 1
postgres=# SELECT p.relname,c.* FROM part_test c, pg_class p WHERE c.tableoid = p.oid order by col1;
relname | col1 | col2 | col3
----------------------------+------+--------+-------------------------------
part_test_p2014_02_21_0330 | 1 | lian1 | 2014-02-21 03:45:01.862785+08
part_test_p2014_02_21_0400 | 2 | lian2 | 2014-02-21 04:15:06.863605+08
part_test_p2014_02_21_0430 | 3 | lian3 | 2014-02-21 04:45:07.144351+08
part_test_p2014_02_21_0500 | 4 | lian4 | 2014-02-21 05:15:05.446265+08
part_test_p2014_02_21_0530 | 5 | lian5 | 2014-02-21 05:45:02.607934+08
part_test_p2014_02_21_0600 | 6 | lian6 | 2014-02-21 06:15:06.643714+08
part_test_p2014_02_21_0630 | 7 | lian7 | 2014-02-21 06:45:03.646074+08
part_test_p2014_02_21_0700 | 8 | lian8 | 2014-02-21 07:15:04.595398+08
part_test_p2014_02_21_0730 | 9 | lian9 | 2014-02-21 07:45:03.498948+08
part_test | 10 | lian10 | 2014-02-21 08:15:03.737789+08
(10 rows)
postgres=# select * from only part_test;
col1 | col2 | col3
------+--------+-------------------------------
10 | lian10 | 2014-02-21 08:15:03.737789+08
(1 row)
{不符合條件的數(shù)據(jù)直接放入了主表中}包括的函數(shù)如下所列：
apply_constraints
drop_constraints
check_name_length
check_parent
check_partition_type
check_unique_column
create_id_function
create_id_partition
create_next_time_partition
create_parent
create_time_function
create_time_partition
create_trigger
drop_partition_id
drop_partition_time
partition_data_id
partition_data_time
reapply_privileges
run_maintenance
show_partitions
undo_partition
undo_partition_id
undo_partition_time
主要函數(shù)用法例舉：
Creation Functions
create_parent(p_parent_table text, p_control text, p_type text, p_interval text, p_constraint_cols text[] DEFAULT NULL, p_premake int DEFAULT 4, p_debug boolean DEFAULT false)
[
創(chuàng)建分區(qū)表函數(shù)，父表必須存在。
p_type分為兩大類(lèi)：基于時(shí)間、基于序列號(hào)，再可細(xì)分為四種類(lèi)型：time-static/time-dynamic/id-static/id-dynamic
Time-static：基于靜態(tài)時(shí)間段，即在生成分區(qū)表時(shí)分別向當(dāng)前時(shí)間段分區(qū)的前后各再生成premake個(gè)分區(qū)表
Time-dynamic：基于動(dòng)態(tài)時(shí)間段，即當(dāng)需要某個(gè)時(shí)間段分區(qū)時(shí)動(dòng)態(tài)生成
Id-static：基于靜態(tài)序列ID，當(dāng)id超出了分區(qū)最大id的50%時(shí)下一個(gè)分區(qū)如果不存在將自動(dòng)會(huì)被創(chuàng)建，不需要使用run_maintenance()函數(shù)創(chuàng)建，其它用法類(lèi)似于time-static，僅支持id>=0
Id-dynamic：基于動(dòng)態(tài)序列ID，用法類(lèi)似于time-dynamic，僅支持id>=0
p_interval為分區(qū)間隔，包括yearly、quarterly、monthly、weekly、daily、hourly、half-hour、quarter-hour、
]
partition_data_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_lock_wait numeric DEFAULT 0)
[將設(shè)置為基于時(shí)間段分區(qū)的父表之前已經(jīng)存在的數(shù)據(jù)重新分布到相應(yīng)的分區(qū)上去，若分區(qū)表不存在將會(huì)被創(chuàng)建，之后自動(dòng)將數(shù)據(jù)遷移過(guò)去]
postgres=# select partman.partition_data_time('test.part_test');
partition_data_time
---------------------
1
(1 row)
{移動(dòng)了一條數(shù)據(jù)}
postgres=# SELECT p.relname,c.* FROM part_test c, pg_class p WHERE c.tableoid = p.oid;
relname | col1 | col2 | col3
----------------------------+------+--------+-------------------------------
part_test_p2014_02_21_0530 | 5 | lian5 | 2014-02-21 05:45:02.607934+08
part_test_p2014_02_21_0500 | 4 | lian4 | 2014-02-21 05:15:05.446265+08
part_test_p2014_02_21_0600 | 6 | lian6 | 2014-02-21 06:15:06.643714+08
part_test_p2014_02_21_0430 | 3 | lian3 | 2014-02-21 04:45:07.144351+08
part_test_p2014_02_21_0630 | 7 | lian7 | 2014-02-21 06:45:03.646074+08
part_test_p2014_02_21_0400 | 2 | lian2 | 2014-02-21 04:15:06.863605+08
part_test_p2014_02_21_0700 | 8 | lian8 | 2014-02-21 07:15:04.595398+08
part_test_p2014_02_21_0330 | 1 | lian1 | 2014-02-21 03:45:01.862785+08
part_test_p2014_02_21_0730 | 9 | lian9 | 2014-02-21 07:45:03.498948+08
part_test_p2014_02_21_0800 | 10 | lian10 | 2014-02-21 08:15:03.737789+08
(10 rows)
{自動(dòng)創(chuàng)建了符合父表中數(shù)據(jù)范圍的分區(qū)表并將數(shù)據(jù)移動(dòng)到新分區(qū)中}partition_data_id(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval int DEFAULT NULL, p_lock_wait numeric DEFAULT 0)
[對(duì)基于id分區(qū)的父表中存在的數(shù)據(jù)進(jìn)行遷移]
postgres=# create table test.part_students(id serial, name text not null, success int not null);
CREATE TABLE
postgres=# select partman.create_parent('test.part_students', 'success', 'id-static', '10');
create_parent
---------------
(1 row)
postgres=# \d+ part_students
Table "test.part_students"
Column | Type | Modifiers | Storage | Stats target | Description
---------+---------+------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('part_students_id_seq'::regclass) | plain | |
name | text | not null | extended | |
success | integer | not null | plain | |
Triggers:
part_students_part_trig BEFORE INSERT ON part_students FOR EACH ROW EXECUTE PROCEDURE part_students_part_trig_func()
Child tables: part_students_p0,
part_students_p10,
part_students_p20,
part_students_p30,
part_students_p40
Has OIDs: no
postgres=# insert into part_students(name,success) values ('lian1',92);
INSERT 0 1
postgres=# insert into part_students(name,success) values ('lian2',88);
INSERT 0 1
postgres=# insert into part_students(name,success) values ('lian3',70);
INSERT 0 1
postgres=# insert into part_students(name,success) values ('lian4',51);
INSERT 0 1
postgres=# SELECT p.relname,c.* FROM part_students c, pg_class p WHERE c.tableoid = p.oid;
relname | id | name | success
---------------+----+-------+---------
part_students | 1 | lian1 | 92
part_students | 4 | lian2 | 88
part_students | 5 | lian3 | 70
part_students | 6 | lian4 | 51
(4 rows)
{因?yàn)闆](méi)有符合條件的分區(qū)，所以所有記錄均插入了主表中}
postgres=# select partman.partition_data_id('test.part_students');
partition_data_id
-------------------
1
(1 row)
{移動(dòng)了一條數(shù)據(jù)}
postgres=# SELECT p.relname,c.* FROM part_students c, pg_class p WHERE c.tableoid = p.oid;
relname | id | name | success
-------------------+----+-------+---------
part_students | 1 | lian1 | 92
part_students | 4 | lian2 | 88
part_students | 5 | lian3 | 70
part_students_p50 | 6 | lian4 | 51
(4 rows)
{正確的創(chuàng)建了分區(qū)并將數(shù)據(jù)遷移}
postgres=# select partman.partition_data_id('test.part_students');
partition_data_id
-------------------
1
(1 row)
postgres=# SELECT p.relname,c.* FROM part_students c, pg_class p WHERE c.tableoid = p.oid;
relname | id | name | success
-------------------+----+-------+---------
part_students | 1 | lian1 | 92
part_students | 4 | lian2 | 88
part_students_p50 | 6 | lian4 | 51
part_students_p70 | 5 | lian3 | 70
(4 rows)一次性將剩下的兩條數(shù)據(jù)一次性批量移動(dòng)：
postgres=# select partman.partition_data_id('test.part_students',2);
partition_data_id
-------------------
2
(1 row)
postgres=# SELECT p.relname,c.* FROM part_students c, pg_class p WHERE c.tableoid = p.oid;
relname | id | name | success
-------------------+----+-------+---------
part_students_p50 | 6 | lian4 | 51
part_students_p70 | 5 | lian3 | 70
part_students_p80 | 4 | lian2 | 88
part_students_p90 | 1 | lian1 | 92
(4 rows)Maintenance Functions
run_maintenance()
[作為計(jì)劃作業(yè)中使用的函數(shù)，作為系統(tǒng)的一個(gè)定時(shí)任務(wù)，定時(shí)對(duì)分區(qū)進(jìn)行維護(hù)，例如自動(dòng)生成新需要的分區(qū)，但不會(huì)對(duì)主表中的數(shù)據(jù)進(jìn)行遷移]
postgres=# \d List of relations Schema | Name | Type | Owner ---------+----------------------------+----------+---------- partman | part_config | table | postgres test | part_test | table | postgres test | part_test_col1_seq | sequence | postgres test | part_test_p2014_02_21_0330 | table | postgres test | part_test_p2014_02_21_0400 | table | postgres test | part_test_p2014_02_21_0430 | table | postgres test | part_test_p2014_02_21_0500 | table | postgres test | part_test_p2014_02_21_0530 | table | postgres test | part_test_p2014_02_21_0600 | table | postgres test | part_test_p2014_02_21_0630 | table | postgres test | part_test_p2014_02_21_0700 | table | postgres test | part_test_p2014_02_21_0730 | table | postgres test | part_test_p2014_02_21_0800 | table | postgres test | part_test_p2014_02_21_0830 | table | postgres test | part_test_p2014_02_21_0900 | table | postgres test | part_test_p2014_02_21_0930 | table | postgres test | part_test_p2014_02_21_1000 | table | postgres test | part_test_p2014_02_21_1030 | table | postgres test | part_test_p2014_02_21_1100 | table | postgres (30 rows) [root@localhost ~]# date -s 10:05:00 Fri Feb 21 10:05:00 CST 2014 postgres=# select partman.run_maintenance(); run_maintenance ----------------- (1 row) postgres=# \d List of relations Schema | Name | Type | Owner ---------+----------------------------+----------+---------- partman | part_config | table | postgres test | part_test | table | postgres test | part_test_col1_seq | sequence | postgres test | part_test_p2014_02_21_0330 | table | postgres test | part_test_p2014_02_21_0400 | table | postgres test | part_test_p2014_02_21_0430 | table | postgres test | part_test_p2014_02_21_0500 | table | postgres test | part_test_p2014_02_21_0530 | table | postgres test | part_test_p2014_02_21_0600 | table | postgres test | part_test_p2014_02_21_0630 | table | postgres test | part_test_p2014_02_21_0700 | table | postgres test | part_test_p2014_02_21_0730 | table | postgres test | part_test_p2014_02_21_0800 | table | postgres test | part_test_p2014_02_21_0830 | table | postgres test | part_test_p2014_02_21_0900 | table | postgres test | part_test_p2014_02_21_0930 | table | postgres test | part_test_p2014_02_21_1000 | table | postgres test | part_test_p2014_02_21_1030 | table | postgres test | part_test_p2014_02_21_1100 | table | postgres test | part_test_p2014_02_21_1130 | table | postgres test | part_test_p2014_02_21_1200 | table | postgres (32 rows)
show_partitions (p_parent_table text, p_order text DEFAULT 'ASC')
[羅列主表的所有分區(qū)表，默認(rèn)按照升序排列]
postgres=# select partman.show_partitions ('test.part_students');
show_partitions
------------------------
test.part_students_p0
test.part_students_p10
test.part_students_p20
test.part_students_p30
test.part_students_p40
test.part_students_p50
test.part_students_p70
test.part_students_p80
test.part_students_p90
(9 rows)check_parent()
[檢查未找到符合的分區(qū)而插入到父表中的條目，并列出父表及條目數(shù)]
postgres=# SELECT p.relname,c.* FROM part_test c, pg_class p WHERE c.tableoid = p.oid;
relname | col1 | col2 | col3
----------------------------+------+--------+-------------------------------
part_test | 10 | lian10 | 2014-02-21 08:15:03.737789+08
part_test_p2014_02_21_0530 | 5 | lian5 | 2014-02-21 05:45:02.607934+08
part_test_p2014_02_21_0500 | 4 | lian4 | 2014-02-21 05:15:05.446265+08
part_test_p2014_02_21_0600 | 6 | lian6 | 2014-02-21 06:15:06.643714+08
part_test_p2014_02_21_0430 | 3 | lian3 | 2014-02-21 04:45:07.144351+08
part_test_p2014_02_21_0630 | 7 | lian7 | 2014-02-21 06:45:03.646074+08
part_test_p2014_02_21_0400 | 2 | lian2 | 2014-02-21 04:15:06.863605+08
part_test_p2014_02_21_0700 | 8 | lian8 | 2014-02-21 07:15:04.595398+08
part_test_p2014_02_21_0330 | 1 | lian1 | 2014-02-21 03:45:01.862785+08
part_test_p2014_02_21_0730 | 9 | lian9 | 2014-02-21 07:45:03.498948+08
(10 rows)
postgres=# select partman.check_parent();
check_parent
--------------------
(test.part_test,1)
(1 row)
{說(shuō)明檢查到主表中存在一條記錄}check_unique_column(p_parent_table text, p_column text)
[檢查指定字段數(shù)據(jù)的唯一性]
postgres=# select * from part_students;
id | name | success
----+-------+---------
7 | lian5 | 64
8 | lian4 | 88
5 | lian3 | 70
4 | lian2 | 88
1 | lian1 | 92
(5 rows)
postgres=# select partman.check_unique_column('test.part_students','success');
NOTICE: v_sql: SELECT success::text AS column_value, count(success) AS count
FROM test.part_students GROUP BY success HAVING (count(success) > 1) ORDER BY success
check_unique_column
---------------------
(88,2)
(1 row)
{表明該字段上存在兩個(gè)88}drop_constraints(p_parent_table text, p_child_table text, p_debug boolean DEFAULT false)
postgres=# \d part_students_p0
Table "test.part_students_p0"
Column | Type | Modifiers
---------+---------+------------------------------------------------------------
id | integer | not null default nextval('part_students_id_seq'::regclass)
name | text | not null
success | integer | not null
Check constraints:
"part_students_p0_partition_check" CHECK (success >= 0 AND success < 10)
Inherits: part_students
postgres=# select partman.drop_constraints('test.part_students','test.part_students_p0');
ERROR: Given parent table (test.part_students) not set up for constraint management (constraint_cols is NULL)
STATEMENT: select partman.drop_constraints('test.part_students','test.part_students_p0');
ERROR: Given parent table (test.part_students) not set up for constraint management (constraint_cols is NULL)
{提示指定的主表中未指定約束字段，這是因?yàn)樵趧?chuàng)建分區(qū)的時(shí)候沒(méi)有指定約束字段}postgres=# create table test.t1(id serial,name text,age int);
CREATE TABLE
postgres=# select partman.create_parent('test.t1', 'id', 'id-static', '5',array['age']);
create_parent
---------------
(1 row)
postgres=# select partman.drop_constraints('test.t1','test.t1_p20');
drop_constraints
------------------
(1 row)
postgres=# \d t1_p20
Table "test.t1_p20"
Column | Type | Modifiers
--------+---------+-------------------------------------------------
id | integer | not null default nextval('t1_id_seq'::regclass)
name | text |
age | integer |
Check constraints:
"t1_p20_partition_check" CHECK (id >= 20 AND id < 25)
Inherits: t1
postgres=# select partman.apply_constraints('test.t1','test.t1_p20');
apply_constraints
-------------------
(1 row)
postgres=# \d t1_p20
Table "test.t1_p20"
Column | Type | Modifiers
--------+---------+-------------------------------------------------
id | integer | not null default nextval('t1_id_seq'::regclass)
name | text |
age | integer |
Check constraints:
"t1_p20_partition_check" CHECK (id >= 20 AND id < 25)
Inherits: t1apply_constraints(p_parent_table text, p_child_table text DEFAULT NULL, p_debug BOOLEAN DEFAULT FALSE)
reapply_privileges(p_parent_table text)
[將父表設(shè)置的權(quán)限重新應(yīng)用到分區(qū)表]
postgres=# create user lian;
CREATE ROLE
postgres=# alter table t1_p0 owner to lian;
ALTER TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
---------+----------------------------+----------+----------
partman | part_config | table | postgres
test | t1 | table | postgres
test | t1_id_seq | sequence | postgres
test | t1_p0 | table | lian
test | t1_p10 | table | postgres
test | t1_p15 &nbs
當(dāng)前標(biāo)題：PostgreSQL從繼承到分區(qū)（三）
當(dāng)前網(wǎng)址：http://weahome.cn/article/gepcdd.html
