postgresql 9.6 分区表测试方案与记录

╰半夏微凉° 2023-06-15 13:29 36阅读 0赞

**一、 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=#