postgresql 9.6 分割槽表測試方案與記錄

天空的鷹發表於2019-11-22

postgresql 9  分割槽表測試


一、 pg 9 準備分割槽表測試環境

在測試環境建立好 pg 9 測試環境,連線到pg 9 例項上:

/usr/local/pgsql101/bin/psql -h 127.0.0.1 -p 5432 -U postgres -d postgres


-- 建立測試庫

\l

CREATE DATABASE pg_9_db;

\l

\c pg_9_db


一、 pg 9 建立新分割槽表


1. 定義父表


CREATE TABLE pg_9_tab( id serial,uid int4,username varchar,create_time bigint);

CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);


2. 定義子表: 用 inherits 建立分割槽表


3. 定義子表約束 :約束資料對應分割槽的規則


-- 按照時間戳分割槽,對應分割槽表與時間戳

2019-09-15 00:00:00  1568476800

2019-10-01 00:00:00  1569859200

2019-11-01 00:00:00  1572537600

2019-12-01 00:00:00  1575129600

2019-12-15 00:00:00  1576339200

2020-01-01 00:00:00  1577808000

2020-02-01 00:00:00  1580486400



上面第2步和第3步,兩步合併在一起,建立分割槽表

CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time < 1569859200 ) ) INHERITS(pg_9_tab);

CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >= 1569859200 and create_time < 1572537600 ) ) INHERITS(pg_9_tab);

CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >= 1572537600 and create_time < 1575129600 ) ) INHERITS(pg_9_tab);

CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >= 1575129600 and create_time < 1577808000 ) ) INHERITS(pg_9_tab);


4. 建立子表索引 :子表不會繼承父表的索引

CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time);

CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time);

CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time);

CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);



5. 建立分割槽插入、修改、刪除函式和觸發器

-- 建立分表insert的路由函式

CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()

    RETURNS trigger

    LANGUAGE plpgsql

AS $function$

BEGIN

    IF ( NEW.create_time < 1569859200 )  THEN

        INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);

    ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN

        INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);

    ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN

        INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);

    ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN

        INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);

    ELSE

        RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';

    END IF;

    RETURN NULL;

END;

$function$;


函式中的 new.* 是指要插入的資料航,在父表上定義插入觸發器:


CREATE TRIGGER insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW

  EXECUTE PROCEDURE pg_9_tab_insert_trigger();

  


6. 啟用分割槽查詢引數:設定 constraint_exclusion 引數

show constraint_exclusion;

SET constraint_exclusion = off;         ##所有表都不通過約束優化查詢

SET constraint_exclusion = on;          ##所有表都通過約束優化查詢

SET constraint_exclusion = partition;    ##之對繼承表和UNION ALL 子查詢通過檢索約束來優化查詢


-- 執行計劃檢視父表還是子表

EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;

EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;



二、 pg 9 插入資料與資料分部確認

-- 插入資料

INSERT INTO  pg_9_tab(uid,username,create_time) 

SELECT round(1000*random()),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);


INSERT INTO  pg_9_tab(uid,username,create_time) SELECT 346,'F',1292083200;




-- 檢視錶

SELECT count(*) FROM pg_9_tab;

SELECT count(*) FROM ONLY pg_9_tab;

\d+ pg_9_tab*


SELECT * FROM pg_9_tab LIMIT 2;

SELECT * FROM pg_9_tab_p_201911  LIMIT 2;


三、 pg 9 分割槽表新增新分割槽

-- 新增分割槽

1.  建立分割槽表

CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );


2.  新增約束

ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_check

    CHECK (create_time >= 1577808000 and create_time < 1580486400);


3.  重新整理觸發器

CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()

    RETURNS trigger

    LANGUAGE plpgsql

AS $function$

BEGIN

    IF ( NEW.create_time < 1569859200 )  THEN

        INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);

    ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN

        INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);

    ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN

        INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);

    ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN

        INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);

    ELSIF ( NEW.create_time >= 1577808000 and NEW.create_time < 1580486400 ) THEN

        INSERT INTO pg_9_tab_p_202001 VALUES (NEW.*);

    ELSE

        RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';

    END IF;

    RETURN NULL;

END;

$function$;


4.  將新分割槽繼承到父表

ALTER TABLE pg_9_tab_p_202001 INHERIT pg_9_tab;


四、 pg 9 分割槽表刪除分割槽

-- 刪除分割槽

DROP TABLE pg_9_tab_p_hisotry;

ALTER TABLE pg_9_tab_p_hisotry NO INHERIT pg_9_tab;


ALTER TABLE pg_9_tab_p_hisotry DETACH PARTITION pg_9_tab;


-- 分割槽查詢

\d pg_9_tab


五、 pg 9 實際測試結果

上面步驟,實際測試結果為:

postgresql 9 分割槽表實際測試過程
-- 時間戳對應轉換:
pg_9_db=# select  extract(epoch from to_timestamp('2019-09-15 00:00:00','yyyy-MM-DD hh24:mi:ss')); 
 date_part  
------------
 1568476800
(1 row)
pg_9_db=# select  extract(epoch from to_timestamp('2019-10-01 00:00:00','yyyy-MM-DD hh24:mi:ss'));    
 date_part  
------------
 1569859200
(1 row)
pg_9_db=# select  extract(epoch from to_timestamp('2019-11-01 00:00:00','yyyy-MM-DD hh24:mi:ss'));      
 date_part  
------------
 1572537600
(1 row)
pg_9_db=# select  extract(epoch from to_timestamp('2019-12-01 00:00:00','yyyy-MM-DD hh24:mi:ss')); 
 date_part  
------------
 1575129600
(1 row)
pg_9_db=# select  extract(epoch from to_timestamp('2019-12-15 00:00:00','yyyy-MM-DD hh24:mi:ss'));  
 date_part  
------------
 1576339200
(1 row)
pg_9_db=# select  extract(epoch from to_timestamp('2020-01-01 00:00:00','yyyy-MM-DD hh24:mi:ss'));      
 date_part  
------------
 1577808000
(1 row)
pg_9_db=# select  extract(epoch from to_timestamp('2020-02-01 00:00:00','yyyy-MM-DD hh24:mi:ss')); 
 date_part  
------------
 1580486400
(1 row)
-- 準備測試環境
postgres=# CREATE DATABASE pg_9_db;
CREATE DATABASE
postgres=# 
postgres=# \l
                                  List of databases
    Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-------------+----------+----------+------------+------------+-----------------------
 dba_test_db | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 pg_9_db     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres    | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
             |          |          |            |            | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
             |          |          |            |            | postgres=CTc/postgres
(5 rows)
postgres=# \c pg_9_db
You are now connected to database "pg_9_db" as user "postgres".
pg_9_db=# 
pg_9_db=# \dt
Did not find any relations.
pg_9_db=# 
pg_9_db=# 
-- 建立分割槽表
pg_9_db=# CREATE TABLE pg_9_tab(
pg_9_db(# id serial,
pg_9_db(# uid int4,
pg_9_db(# username varchar,
pg_9_db(# create_time bigint
pg_9_db(# );
CREATE TABLE
pg_9_db=# 
pg_9_db=# CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);
CREATE INDEX
pg_9_db=# 
pg_9_db=# \d+ pg_9_tab
                                                        Table "public.pg_9_tab"
   Column    |       Type        | Collation | Nullable |               Default                | Storage  | Stats target | Description 
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer           |           | not null | nextval('pg_9_tab_id_seq'::regclass) | plain    |              | 
 uid         | integer           |           |          |                                      | plain    |              | 
 username    | character varying |           |          |                                      | extended |              | 
 create_time | bigint            |           |          |                                      | plain    |              | 
Indexes:
    "idx_pg_9_tab_ctime" btree (create_time)
pg_9_db=# 
pg_9_db=# CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time < 1569859200 ) ) INHERITS(pg_9_tab);
CREATE TABLE
pg_9_db=# CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >= 1569859200 and create_time < 1572537600 ) ) INHERITS(pg_9_tab);
CREATE TABLE
pg_9_db=# CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >= 1572537600 and create_time < 1575129600 ) ) INHERITS(pg_9_tab);
CREATE TABLE
pg_9_db=# CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >= 1575129600 and create_time < 1577808000 ) ) INHERITS(pg_9_tab);
CREATE TABLE
pg_9_db=# 
pg_9_db=# \d+ pg_9_tab_p_hisotry
                                                   Table "public.pg_9_tab_p_hisotry"
   Column    |       Type        | Collation | Nullable |               Default                | Storage  | Stats target | Description 
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer           |           | not null | nextval('pg_9_tab_id_seq'::regclass) | plain    |              | 
 uid         | integer           |           |          |                                      | plain    |              | 
 username    | character varying |           |          |                                      | extended |              | 
 create_time | bigint            |           |          |                                      | plain    |              | 
Check constraints:
    "pg_9_tab_p_hisotry_create_time_check" CHECK (create_time < 1569859200)
Inherits: pg_9_tab
pg_9_db=# 
pg_9_db=# CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time);
CREATE INDEX
pg_9_db=# CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time);
CREATE INDEX
pg_9_db=# CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time);
CREATE INDEX
pg_9_db=# CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);
CREATE INDEX
pg_9_db=# 
pg_9_db=# \d+ pg_9_tab_p_hisotry
                                                   Table "public.pg_9_tab_p_hisotry"
   Column    |       Type        | Collation | Nullable |               Default                | Storage  | Stats target | Description 
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer           |           | not null | nextval('pg_9_tab_id_seq'::regclass) | plain    |              | 
 uid         | integer           |           |          |                                      | plain    |              | 
 username    | character varying |           |          |                                      | extended |              | 
 create_time | bigint            |           |          |                                      | plain    |              | 
Indexes:
    "idx_pg_9_tab_p_hisotry_ctime" btree (create_time)
Check constraints:
    "pg_9_tab_p_hisotry_create_time_check" CHECK (create_time < 1569859200)
Inherits: pg_9_tab
pg_9_db=# 
pg_9_db=# 
pg_9_db=# 
pg_9_db=# CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
pg_9_db-#     RETURNS trigger
pg_9_db-#     LANGUAGE plpgsql
pg_9_db-# AS $function$
pg_9_db$# BEGIN
pg_9_db$#     IF ( NEW.create_time < 1569859200 )  THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
pg_9_db$#     ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
pg_9_db$#     ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
pg_9_db$#     ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
pg_9_db$#     ELSE
pg_9_db$#         RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
pg_9_db$#     END IF;
pg_9_db$#     RETURN NULL;
pg_9_db$# END;
pg_9_db$# $function$;
CREATE FUNCTION
pg_9_db=# 
pg_9_db=# 
-- 測試資料插入與分佈情況
pg_9_db=# 
pg_9_db=# select count(*) from pg_9_tab;
 count 
-------
     0
(1 row)
pg_9_db=# 
pg_9_db=# select count(*) from ONLY pg_9_tab;
 count 
-------
     0
(1 row)
pg_9_db=# INSERT INTO  pg_9_tab(uid,username,create_time) 
pg_9_db-# SELECT round(1000*random()),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);
INSERT 0 0
pg_9_db=# 
pg_9_db=# select count(*) from pg_9_tab;
 count 
-------
 21841
(1 row)
pg_9_db=# select count(*) from ONLY pg_9_tab;
 count 
-------
     0
(1 row)
pg_9_db=# 
pg_9_db=# select * from pg_9_tab limit 2;
   id   | uid | username | create_time 
--------+-----+----------+-------------
 131227 | 638 | Y        |  1568476800
 131228 | 523 | E        |  1568477160
(2 rows)
pg_9_db=# 
pg_9_db=# select * from pg_9_tab_p_201912 limit 2;
   id   | uid | username | create_time 
--------+-----+----------+-------------
 149707 | 892 | [        |  1575129600
 149708 |  28 | V        |  1575129960
(2 rows)
pg_9_db=# 
pg_9_db=#  \dt+ pg_9_tab*
                             List of relations
 Schema |        Name        | Type  |  Owner   |    Size    | Description 
--------+--------------------+-------+----------+------------+-------------
 public | pg_9_tab           | table | postgres | 8192 bytes | 
 public | pg_9_tab_p_201910  | table | postgres | 416 kB     | 
 public | pg_9_tab_p_201911  | table | postgres | 400 kB     | 
 public | pg_9_tab_p_201912  | table | postgres | 208 kB     | 
 public | pg_9_tab_p_hisotry | table | postgres | 232 kB     | 
(5 rows)
-- 執行計劃與引數
pg_9_db=# show constraint_exclusion;
 constraint_exclusion 
----------------------
 partition
(1 row)
pg_9_db=# 
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..72.41 rows=3362 width=18) (actual time=0.017..1.424 rows=3359 loops=1)
   ->  Seq Scan on pg_9_tab  (cost=0.00..0.00 rows=1 width=48) (actual time=0.006..0.006 rows=0 loops=1)
         Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
   ->  Seq Scan on pg_9_tab_p_201912  (cost=0.00..72.41 rows=3361 width=18) (actual time=0.010..1.007 rows=3359 loops=1)
         Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
         Rows Removed by Filter: 2
 Planning time: 0.982 ms
 Execution time: 1.720 ms
(8 rows)
pg_9_db=# 
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
                                                                   QUERY PLAN                                                 
                  
------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910  (cost=0.28..8.30 rows=1 width=18) (actual time=0.039..0.03
9 rows=0 loops=1)
   Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
 Planning time: 0.271 ms
 Execution time: 0.066 ms
(4 rows)
pg_9_db=# 
pg_9_db=# SET constraint_exclusion = off;
SET
pg_9_db=# 
pg_9_db=# show constraint_exclusion;
 constraint_exclusion 
----------------------
 off
(1 row)
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
                                                                       QUERY PLAN                                             
                          
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..97.32 rows=3365 width=18) (actual time=0.066..1.446 rows=3359 loops=1)
   ->  Seq Scan on pg_9_tab  (cost=0.00..0.00 rows=1 width=48) (actual time=0.005..0.005 rows=0 loops=1)
         Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
   ->  Index Scan using idx_pg_9_tab_p_hisotry_ctime on pg_9_tab_p_hisotry  (cost=0.28..8.30 rows=1 width=18) (actual time=0.0
16..0.016 rows=0 loops=1)
         Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
   ->  Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910  (cost=0.28..8.30 rows=1 width=18) (actual time=0.020
..0.020 rows=0 loops=1)
         Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
   ->  Index Scan using idx_pg_9_tab_p_201911_ctime on pg_9_tab_p_201911  (cost=0.28..8.30 rows=1 width=18) (actual time=0.014
..0.014 rows=0 loops=1)
         Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
   ->  Seq Scan on pg_9_tab_p_201912  (cost=0.00..72.41 rows=3361 width=18) (actual time=0.010..0.981 rows=3359 loops=1)
         Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
         Rows Removed by Filter: 2
 Planning time: 0.811 ms
 Execution time: 1.796 ms
(14 rows)
pg_9_db=# 
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
                                                                   QUERY PLAN                                                 
                  
------------------------------------------------------------------------------------------------------------------------------
------------------
 Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910  (cost=0.28..8.30 rows=1 width=18) (actual time=0.008..0.00
8 rows=0 loops=1)
   Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
 Planning time: 0.147 ms
 Execution time: 0.031 ms
(4 rows)
pg_9_db=# 
pg_9_db=# SET constraint_exclusion = on;
SET
pg_9_db=# 
pg_9_db=# show constraint_exclusion;
 constraint_exclusion 
----------------------
 on
(1 row)
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..72.41 rows=3362 width=18) (actual time=0.029..1.401 rows=3359 loops=1)
   ->  Seq Scan on pg_9_tab  (cost=0.00..0.00 rows=1 width=48) (actual time=0.012..0.012 rows=0 loops=1)
         Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
   ->  Seq Scan on pg_9_tab_p_201912  (cost=0.00..72.41 rows=3361 width=18) (actual time=0.016..0.982 rows=3359 loops=1)
         Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
         Rows Removed by Filter: 2
 Planning time: 0.402 ms
 Execution time: 1.782 ms
(8 rows)
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=48) (actual time=0.001..0.001 rows=0 loops=1)
   One-Time Filter: false
 Planning time: 0.122 ms
 Execution time: 0.016 ms
(4 rows)
pg_9_db=# 
-- 新增新分割槽
pg_9_db-# 
pg_9_db-# \d+ pg_9_tab
                                                        Table "public.pg_9_tab"
   Column    |       Type        | Collation | Nullable |               Default                | Storage  | Stats target | Des
cription 
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+----
---------
 id          | integer           |           | not null | nextval('pg_9_tab_id_seq'::regclass) | plain    |              | 
 uid         | integer           |           |          |                                      | plain    |              | 
 username    | character varying |           |          |                                      | extended |              | 
 create_time | bigint            |           |          |                                      | plain    |              | 
Indexes:
    "idx_pg_9_tab_ctime" btree (create_time)
Triggers:
    insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()
Child tables: pg_9_tab_p_201910,
              pg_9_tab_p_201911,
              pg_9_tab_p_201912,
              pg_9_tab_p_hisotry
pg_9_db-# 
pg_9_db-# 
pg_9_db-# CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );
ERROR:  syntax error at or near "、"
LINE 1: 、
        ^
pg_9_db=# 
pg_9_db=# CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );
CREATE TABLE
pg_9_db=# 
pg_9_db=# ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_check
pg_9_db-#     CHECK (create_time >= 1577808000 and create_time < 1580486400);
ALTER TABLE
pg_9_db=# 
pg_9_db=# CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
pg_9_db-#     RETURNS trigger
pg_9_db-#     LANGUAGE plpgsql
pg_9_db-# AS $function$
pg_9_db$# BEGIN
pg_9_db$#     IF ( NEW.create_time < 1569859200 )  THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
pg_9_db$#     ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
pg_9_db$#     ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
pg_9_db$#     ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
pg_9_db$#     ELSIF ( NEW.create_time >= 1577808000 and NEW.create_time < 1580486400 ) THEN
pg_9_db$#         INSERT INTO pg_9_tab_p_202001 VALUES (NEW.*);
pg_9_db$#     ELSE
pg_9_db$#         RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
pg_9_db$#     END IF;
pg_9_db$#     RETURN NULL;
pg_9_db$# END;
pg_9_db$# $function$;
CREATE FUNCTION
pg_9_db=# 
pg_9_db=# ALTER TABLE pg_9_tab_p_202001 INHERIT pg_9_tab;
ALTER TABLE
pg_9_db=# 
pg_9_db=# \d+ pg_9_tab
                                                        Table "public.pg_9_tab"
   Column    |       Type        | Collation | Nullable |               Default                | Storage  | Stats target | Description 
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer           |           | not null | nextval('pg_9_tab_id_seq'::regclass) | plain    |              | 
 uid         | integer           |           |          |                                      | plain    |              | 
 username    | character varying |           |          |                                      | extended |              | 
 create_time | bigint            |           |          |                                      | plain    |              | 
Indexes:
    "idx_pg_9_tab_ctime" btree (create_time)
Triggers:
    insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()
Child tables: pg_9_tab_p_201910,
              pg_9_tab_p_201911,
              pg_9_tab_p_201912,
              pg_9_tab_p_202001,
              pg_9_tab_p_hisotry
pg_9_db=# 
-- 刪除分割槽
pg_9_db=# \d+ pg_9_tab
                                                        Table "public.pg_9_tab"
   Column    |       Type        | Collation | Nullable |               Default                | Storage  | Stats target | Description 
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer           |           | not null | nextval('pg_9_tab_id_seq'::regclass) | plain    |              | 
 uid         | integer           |           |          |                                      | plain    |              | 
 username    | character varying |           |          |                                      | extended |              | 
 create_time | bigint            |           |          |                                      | plain    |              | 
Indexes:
    "idx_pg_9_tab_ctime" btree (create_time)
Triggers:
    insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()
Child tables: pg_9_tab_p_201910,
              pg_9_tab_p_201911,
              pg_9_tab_p_201912,
              pg_9_tab_p_202001,
              pg_9_tab_p_hisotry
pg_9_db=# 
pg_9_db=# ALTER TABLE pg_9_tab_p_hisotry NO INHERIT pg_9_tab;
ALTER TABLE
pg_9_db=# 
pg_9_db=# \d+ pg_9_tab
                                                        Table "public.pg_9_tab"
   Column    |       Type        | Collation | Nullable |               Default                | Storage  | Stats target | Description 
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | integer           |           | not null | nextval('pg_9_tab_id_seq'::regclass) | plain    |              | 
 uid         | integer           |           |          |                                      | plain    |              | 
 username    | character varying |           |          |                                      | extended |              | 
 create_time | bigint            |           |          |                                      | plain    |              | 
Indexes:
    "idx_pg_9_tab_ctime" btree (create_time)
Triggers:
    insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()
Child tables: pg_9_tab_p_201910,
              pg_9_tab_p_201911,
              pg_9_tab_p_201912,
              pg_9_tab_p_202001
pg_9_db=# DROP TABLE pg_9_tab_p_hisotry;
DROP TABLE
pg_9_db=#


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24638123/viewspace-2665400/,如需轉載,請註明出處,否則將追究法律責任。

相關文章