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

2025-04-16 技术教程

postgresql 9 分区表测试

一、pg 9准备分区表测试环境

在测试环境创建好pg 9测试环境，连接到pg 9实例上：

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

--创建测试库

CREATE DATABASE pg_9_db;

\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 * FROMpg_9_tab LIMIT 2;

SELECT * FROMpg_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实际测试结果

上面步骤，实际测试结果为：

postgresql9分区表实际测试过程--时间戳对应转换：pg_9_db=#selectextract(epochfromto_timestamp('2019-09-1500:00:00','yyyy-MM-DDhh34:mi:ss'));date_part------------1568476800(1row)pg_9_db=#selectextract(epochfromto_timestamp('2019-10-0100:00:00','yyyy-MM-DDhh34:mi:ss'));date_part------------1569859200(1row)pg_9_db=#selectextract(epochfromto_timestamp('2019-11-0100:00:00','yyyy-MM-DDhh34:mi:ss'));date_part------------1572537600(1row)pg_9_db=#selectextract(epochfromto_timestamp('2019-12-0100:00:00','yyyy-MM-DDhh34:mi:ss'));date_part------------1575129600(1row)pg_9_db=#selectextract(epochfromto_timestamp('2019-12-1500:00:00','yyyy-MM-DDhh34:mi:ss'));date_part------------1576339200(1row)pg_9_db=#selectextract(epochfromto_timestamp('2020-01-0100:00:00','yyyy-MM-DDhh34:mi:ss'));date_part------------1577808000(1row)pg_9_db=#selectextract(epochfromto_timestamp('2020-02-0100:00:00','yyyy-MM-DDhh34:mi:ss'));date_part------------1580486400(1row)--准备测试环境postgres=#CREATEDATABASEpg_9_db;CREATEDATABASEpostgres=#postgres=#\lListofdatabasesName|Owner|Encoding|Collate|Ctype|Accessprivileges-------------+----------+----------+------------+------------+-----------------------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/postgrestemplate1|postgres|UTF8|en_US.utf8|en_US.utf8|=c/postgres+|||||postgres=CTc/postgres(5rows)postgres=#\cpg_9_dbYouarenowconnectedtodatabase"pg_9_db"asuser"postgres".pg_9_db=#pg_9_db=#\dtDidnotfindanyrelations.pg_9_db=#pg_9_db=#--创建分区表pg_9_db=#CREATETABLEpg_9_tab(pg_9_db(#idserial,pg_9_db(#uidint4,pg_9_db(#usernamevarchar,pg_9_db(#create_timebigintpg_9_db(#);CREATETABLEpg_9_db=#pg_9_db=#CREATEINDEXidx_pg_9_tab_ctimeONpg_9_tabUSINGbtree(create_time);CREATEINDEXpg_9_db=#pg_9_db=#\d+pg_9_tabTable"public.pg_9_tab"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------id|integer||notnull|nextval('pg_9_tab_id_seq'::regclass)|plain||uid|integer||||plain||username|charactervarying||||extended||create_time|bigint||||plain||Indexes:"idx_pg_9_tab_ctime"btree(create_time)pg_9_db=#pg_9_db=#CREATETABLEpg_9_tab_p_hisotry(CHECK(create_time<1569859200))INHERITS(pg_9_tab);CREATETABLEpg_9_db=#CREATETABLEpg_9_tab_p_201910(CHECK(create_time>=1569859200andcreate_time<1572537600))INHERITS(pg_9_tab);CREATETABLEpg_9_db=#CREATETABLEpg_9_tab_p_201911(CHECK(create_time>=1572537600andcreate_time<1575129600))INHERITS(pg_9_tab);CREATETABLEpg_9_db=#CREATETABLEpg_9_tab_p_201912(CHECK(create_time>=1575129600andcreate_time<1577808000))INHERITS(pg_9_tab);CREATETABLEpg_9_db=#pg_9_db=#\d+pg_9_tab_p_hisotryTable"public.pg_9_tab_p_hisotry"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------id|integer||notnull|nextval('pg_9_tab_id_seq'::regclass)|plain||uid|integer||||plain||username|charactervarying||||extended||create_time|bigint||||plain||Checkconstraints:"pg_9_tab_p_hisotry_create_time_check"CHECK(create_time<1569859200)Inherits:pg_9_tabpg_9_db=#pg_9_db=#CREATEINDEXidx_pg_9_tab_p_hisotry_ctimeONpg_9_tab_p_hisotryUSINGbtree(create_time);CREATEINDEXpg_9_db=#CREATEINDEXidx_pg_9_tab_p_201910_ctimeONpg_9_tab_p_201910USINGbtree(create_time);CREATEINDEXpg_9_db=#CREATEINDEXidx_pg_9_tab_p_201911_ctimeONpg_9_tab_p_201911USINGbtree(create_time);CREATEINDEXpg_9_db=#CREATEINDEXidx_pg_9_tab_p_201912_ctimeONpg_9_tab_p_201912USINGbtree(create_time);CREATEINDEXpg_9_db=#pg_9_db=#\d+pg_9_tab_p_hisotryTable"public.pg_9_tab_p_hisotry"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------id|integer||notnull|nextval('pg_9_tab_id_seq'::regclass)|plain||uid|integer||||plain||username|charactervarying||||extended||create_time|bigint||||plain||Indexes:"idx_pg_9_tab_p_hisotry_ctime"btree(create_time)Checkconstraints:"pg_9_tab_p_hisotry_create_time_check"CHECK(create_time<1569859200)Inherits:pg_9_tabpg_9_db=#pg_9_db=#pg_9_db=#pg_9_db=#CREATEORREPLACEFUNCTIONpg_9_tab_insert_trigger()pg_9_db-#RETURNStriggerpg_9_db-#LANGUAGEplpgsqlpg_9_db-#AS$function$pg_9_db$#BEGINpg_9_db$#IF(NEW.create_time<1569859200)THENpg_9_db$#INSERTINTOpg_9_tab_p_hisotryVALUES(NEW.*);pg_9_db$#ELSIF(NEW.create_time>=1569859200andNEW.create_time<1572537600)THENpg_9_db$#INSERTINTOpg_9_tab_p_201910VALUES(NEW.*);pg_9_db$#ELSIF(NEW.create_time>=1572537600andNEW.create_time<1575129600)THENpg_9_db$#INSERTINTOpg_9_tab_p_201911VALUES(NEW.*);pg_9_db$#ELSIF(NEW.create_time>=1575129600andNEW.create_time<1577808000)THENpg_9_db$#INSERTINTOpg_9_tab_p_201912VALUES(NEW.*);pg_9_db$#ELSEpg_9_db$#RAISEEXCEPTION'create_timeoutofrange.Fixthepg_9_tab_insert_trigger()function!';pg_9_db$#ENDIF;pg_9_db$#RETURNNULL;pg_9_db$#END;pg_9_db$#$function$;CREATEFUNCTIONpg_9_db=#pg_9_db=#--测试数据插入与分布情况pg_9_db=#pg_9_db=#selectcount(*)frompg_9_tab;count-------0(1row)pg_9_db=#pg_9_db=#selectcount(*)fromONLYpg_9_tab;count-------0(1row)pg_9_db=#INSERTINTOpg_9_tab(uid,username,create_time)pg_9_db-#SELECTround(1000*random()),chr(int4(random()*26)+65),generate_series(1568476800,1576339200,360);INSERT00pg_9_db=#pg_9_db=#selectcount(*)frompg_9_tab;count-------21841(1row)pg_9_db=#selectcount(*)fromONLYpg_9_tab;count-------0(1row)pg_9_db=#pg_9_db=#select*frompg_9_tablimit2;id|uid|username|create_time--------+-----+----------+-------------131227|638|Y|1568476800131228|523|E|1568477160(2rows)pg_9_db=#pg_9_db=#select*frompg_9_tab_p_201912limit2;id|uid|username|create_time--------+-----+----------+-------------149707|892|[|1575129600149708|28|V|1575129960(2rows)pg_9_db=#pg_9_db=#\dt+pg_9_tab*ListofrelationsSchema|Name|Type|Owner|Size|Description--------+--------------------+-------+----------+------------+-------------public|pg_9_tab|table|postgres|8192bytes|public|pg_9_tab_p_201910|table|postgres|416kB|public|pg_9_tab_p_201911|table|postgres|400kB|public|pg_9_tab_p_201912|table|postgres|208kB|public|pg_9_tab_p_hisotry|table|postgres|232kB|(5rows)--执行计划与参数pg_9_db=#showconstraint_exclusion;constraint_exclusion----------------------partition(1row)pg_9_db=#pg_9_db=#EXPLAINANALYZESELECT*frompg_9_tabWHEREcreate_time>1575129600ANDcreate_time<1576339200;QUERYPLAN-------------------------------------------------------------------------------------------------------------------------Append(cost=0.00..72.41rows=3362width=18)(actualtime=0.017..1.424rows=3359loops=1)->SeqScanonpg_9_tab(cost=0.00..0.00rows=1width=48)(actualtime=0.006..0.006rows=0loops=1)Filter:((create_time>1575129600)AND(create_time<1576339200))->SeqScanonpg_9_tab_p_201912(cost=0.00..72.41rows=3361width=18)(actualtime=0.010..1.007rows=3359loops=1)Filter:((create_time>1575129600)AND(create_time<1576339200))RowsRemovedbyFilter:2Planningtime:0.982msExecutiontime:1.720ms(8rows)pg_9_db=#pg_9_db=#EXPLAINANALYZESELECT*frompg_9_tab_p_201910WHEREcreate_time>1575129600ANDcreate_time<1576339200;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------------------IndexScanusingidx_pg_9_tab_p_201910_ctimeonpg_9_tab_p_201910(cost=0.28..8.30rows=1width=18)(actualtime=0.039..0.039rows=0loops=1)IndexCond:((create_time>1575129600)AND(create_time<1576339200))Planningtime:0.271msExecutiontime:0.066ms(4rows)pg_9_db=#pg_9_db=#SETconstraint_exclusion=off;SETpg_9_db=#pg_9_db=#showconstraint_exclusion;constraint_exclusion----------------------off(1row)pg_9_db=#EXPLAINANALYZESELECT*frompg_9_tabWHEREcreate_time>1575129600ANDcreate_time<1576339200;QUERYPLAN--------------------------------------------------------------------------------------------------------------------------------------------------------Append(cost=0.00..97.32rows=3365width=18)(actualtime=0.066..1.446rows=3359loops=1)->SeqScanonpg_9_tab(cost=0.00..0.00rows=1width=48)(actualtime=0.005..0.005rows=0loops=1)Filter:((create_time>1575129600)AND(create_time<1576339200))->IndexScanusingidx_pg_9_tab_p_hisotry_ctimeonpg_9_tab_p_hisotry(cost=0.28..8.30rows=1width=18)(actualtime=0.016..0.016rows=0loops=1)IndexCond:((create_time>1575129600)AND(create_time<1576339200))->IndexScanusingidx_pg_9_tab_p_201910_ctimeonpg_9_tab_p_201910(cost=0.28..8.30rows=1width=18)(actualtime=0.020..0.020rows=0loops=1)IndexCond:((create_time>1575129600)AND(create_time<1576339200))->IndexScanusingidx_pg_9_tab_p_201911_ctimeonpg_9_tab_p_201911(cost=0.28..8.30rows=1width=18)(actualtime=0.014..0.014rows=0loops=1)IndexCond:((create_time>1575129600)AND(create_time<1576339200))->SeqScanonpg_9_tab_p_201912(cost=0.00..72.41rows=3361width=18)(actualtime=0.010..0.981rows=3359loops=1)Filter:((create_time>1575129600)AND(create_time<1576339200))RowsRemovedbyFilter:2Planningtime:0.811msExecutiontime:1.796ms(14rows)pg_9_db=#pg_9_db=#EXPLAINANALYZESELECT*frompg_9_tab_p_201910WHEREcreate_time>1575129600ANDcreate_time<1576339200;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------------------IndexScanusingidx_pg_9_tab_p_201910_ctimeonpg_9_tab_p_201910(cost=0.28..8.30rows=1width=18)(actualtime=0.008..0.008rows=0loops=1)IndexCond:((create_time>1575129600)AND(create_time<1576339200))Planningtime:0.147msExecutiontime:0.031ms(4rows)pg_9_db=#pg_9_db=#SETconstraint_exclusion=on;SETpg_9_db=#pg_9_db=#showconstraint_exclusion;constraint_exclusion----------------------on(1row)pg_9_db=#EXPLAINANALYZESELECT*frompg_9_tabWHEREcreate_time>1575129600ANDcreate_time<1576339200;QUERYPLAN-------------------------------------------------------------------------------------------------------------------------Append(cost=0.00..72.41rows=3362width=18)(actualtime=0.029..1.401rows=3359loops=1)->SeqScanonpg_9_tab(cost=0.00..0.00rows=1width=48)(actualtime=0.012..0.012rows=0loops=1)Filter:((create_time>1575129600)AND(create_time<1576339200))->SeqScanonpg_9_tab_p_201912(cost=0.00..72.41rows=3361width=18)(actualtime=0.016..0.982rows=3359loops=1)Filter:((create_time>1575129600)AND(create_time<1576339200))RowsRemovedbyFilter:2Planningtime:0.402msExecutiontime:1.782ms(8rows)pg_9_db=#EXPLAINANALYZESELECT*frompg_9_tab_p_201910WHEREcreate_time>1575129600ANDcreate_time<1576339200;QUERYPLAN-------------------------------------------------------------------------------------Result(cost=0.00..0.00rows=0width=48)(actualtime=0.001..0.001rows=0loops=1)One-TimeFilter:falsePlanningtime:0.122msExecutiontime:0.016ms(4rows)pg_9_db=#--添加新分区pg_9_db-#pg_9_db-#\d+pg_9_tabTable"public.pg_9_tab"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------id|integer||notnull|nextval('pg_9_tab_id_seq'::regclass)|plain||uid|integer||||plain||username|charactervarying||||extended||create_time|bigint||||plain||Indexes:"idx_pg_9_tab_ctime"btree(create_time)Triggers:insert_pg_9_tab_triggerBEFOREINSERTONpg_9_tabFOREACHROWEXECUTEPROCEDUREpg_9_tab_insert_trigger()Childtables:pg_9_tab_p_201910,pg_9_tab_p_201911,pg_9_tab_p_201912,pg_9_tab_p_hisotrypg_9_db-#pg_9_db-#pg_9_db-#CREATETABLEpg_9_tab_p_202001(LIKEpg_9_tabINCLUDINGALL);ERROR:syntaxerroratornear"、"LINE1:、^pg_9_db=#pg_9_db=#CREATETABLEpg_9_tab_p_202001(LIKEpg_9_tabINCLUDINGALL);CREATETABLEpg_9_db=#pg_9_db=#ALTERTABLEpg_9_tab_p_202001ADDCONSTRAINTpg_9_tab_create_time_checkpg_9_db-#CHECK(create_time>=1577808000andcreate_time<1580486400);ALTERTABLEpg_9_db=#pg_9_db=#CREATEORREPLACEFUNCTIONpg_9_tab_insert_trigger()pg_9_db-#RETURNStriggerpg_9_db-#LANGUAGEplpgsqlpg_9_db-#AS$function$pg_9_db$#BEGINpg_9_db$#IF(NEW.create_time<1569859200)THENpg_9_db$#INSERTINTOpg_9_tab_p_hisotryVALUES(NEW.*);pg_9_db$#ELSIF(NEW.create_time>=1569859200andNEW.create_time<1572537600)THENpg_9_db$#INSERTINTOpg_9_tab_p_201910VALUES(NEW.*);pg_9_db$#ELSIF(NEW.create_time>=1572537600andNEW.create_time<1575129600)THENpg_9_db$#INSERTINTOpg_9_tab_p_201911VALUES(NEW.*);pg_9_db$#ELSIF(NEW.create_time>=1575129600andNEW.create_time<1577808000)THENpg_9_db$#INSERTINTOpg_9_tab_p_201912VALUES(NEW.*);pg_9_db$#ELSIF(NEW.create_time>=1577808000andNEW.create_time<1580486400)THENpg_9_db$#INSERTINTOpg_9_tab_p_202001VALUES(NEW.*);pg_9_db$#ELSEpg_9_db$#RAISEEXCEPTION'create_timeoutofrange.Fixthepg_9_tab_insert_trigger()function!';pg_9_db$#ENDIF;pg_9_db$#RETURNNULL;pg_9_db$#END;pg_9_db$#$function$;CREATEFUNCTIONpg_9_db=#pg_9_db=#ALTERTABLEpg_9_tab_p_202001INHERITpg_9_tab;ALTERTABLEpg_9_db=#pg_9_db=#\d+pg_9_tabTable"public.pg_9_tab"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------id|integer||notnull|nextval('pg_9_tab_id_seq'::regclass)|plain||uid|integer||||plain||username|charactervarying||||extended||create_time|bigint||||plain||Indexes:"idx_pg_9_tab_ctime"btree(create_time)Triggers:insert_pg_9_tab_triggerBEFOREINSERTONpg_9_tabFOREACHROWEXECUTEPROCEDUREpg_9_tab_insert_trigger()Childtables:pg_9_tab_p_201910,pg_9_tab_p_201911,pg_9_tab_p_201912,pg_9_tab_p_202001,pg_9_tab_p_hisotrypg_9_db=#--删除分区pg_9_db=#\d+pg_9_tabTable"public.pg_9_tab"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------id|integer||notnull|nextval('pg_9_tab_id_seq'::regclass)|plain||uid|integer||||plain||username|charactervarying||||extended||create_time|bigint||||plain||Indexes:"idx_pg_9_tab_ctime"btree(create_time)Triggers:insert_pg_9_tab_triggerBEFOREINSERTONpg_9_tabFOREACHROWEXECUTEPROCEDUREpg_9_tab_insert_trigger()Childtables:pg_9_tab_p_201910,pg_9_tab_p_201911,pg_9_tab_p_201912,pg_9_tab_p_202001,pg_9_tab_p_hisotrypg_9_db=#pg_9_db=#ALTERTABLEpg_9_tab_p_hisotryNOINHERITpg_9_tab;ALTERTABLEpg_9_db=#pg_9_db=#\d+pg_9_tabTable"public.pg_9_tab"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------id|integer||notnull|nextval('pg_9_tab_id_seq'::regclass)|plain||uid|integer||||plain||username|charactervarying||||extended||create_time|bigint||||plain||Indexes:"idx_pg_9_tab_ctime"btree(create_time)Triggers:insert_pg_9_tab_triggerBEFOREINSERTONpg_9_tabFOREACHROWEXECUTEPROCEDUREpg_9_tab_insert_trigger()Childtables:pg_9_tab_p_201910,pg_9_tab_p_201911,pg_9_tab_p_201912,pg_9_tab_p_202001pg_9_db=#DROPTABLEpg_9_tab_p_hisotry;DROPTABLEpg_9_db=#