In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Postgresql 9 Partition Table Test
1. Pg 9 prepares the partition table test environment
Create a pg 9 test environment in the test environment and connect to the pg 9 instance:
/ usr/local/pgsql101/bin/psql-h 127.0.0.1-p 5432-U postgres-d postgres
-- create a test library
\ l
CREATE DATABASE pg_9_db
\ l
\ C pg_9_db
1. Pg 9 creates a new partition table
1. Define the parent table
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)
two。 Define child tables: create partition tables with inherits
3. Defining subtable constraints: rules that constrain data corresponding to partitions
-- Partition according to timestamp, corresponding to partition table and timestamp
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
Steps 2 and 3 above are merged to create a partition table
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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34:mi:ss')); date_part ------------ 1580486400(1 row)-- 准备测试环境postgres=# CREATE DATABASE pg_9_db;CREATE DATABASEpostgres=# 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_dbYou are now connected to database "pg_9_db" as user "postgres".pg_9_db=# pg_9_db=# \dtDid 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 bigintpg_9_db(# );CREATE TABLEpg_9_db=# pg_9_db=# CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);CREATE INDEXpg_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 TABLEpg_9_db=# CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >= 1569859200 and create_time
< 1572537600 ) ) INHERITS(pg_9_tab);CREATE TABLEpg_9_db=# CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >= 1572537600 and create_time
< 1575129600 ) ) INHERITS(pg_9_tab);CREATE TABLEpg_9_db=# CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >= 1575129600 and create_time
< 1577808000 ) ) INHERITS(pg_9_tab);CREATE TABLEpg_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_tabpg_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 INDEXpg_9_db=# CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time);CREATE INDEXpg_9_db=# CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time);CREATE INDEXpg_9_db=# CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);CREATE INDEXpg_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_tabpg_9_db=# pg_9_db=# pg_9_db=# pg_9_db=# CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()pg_9_db-# RETURNS triggerpg_9_db-# LANGUAGE plpgsqlpg_9_db-# AS $function$pg_9_db$# BEGINpg_9_db$# IF ( NEW.create_time < 1569859200 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time
< 1572537600 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time
< 1575129600 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time
< 1577808000 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);pg_9_db$# ELSEpg_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 FUNCTIONpg_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 0pg_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.039 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;SETpg_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.016..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.008 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;SETpg_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 | 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_hisotrypg_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 TABLEpg_9_db=# pg_9_db=# ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_checkpg_9_db-# CHECK (create_time >= 1577808000 and create_time
< 1580486400);ALTER TABLEpg_9_db=# pg_9_db=# CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()pg_9_db-# RETURNS triggerpg_9_db-# LANGUAGE plpgsqlpg_9_db-# AS $function$pg_9_db$# BEGINpg_9_db$# IF ( NEW.create_time < 1569859200 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time
< 1572537600 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time
< 1575129600 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time
< 1577808000 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >= 1577808000 and NEW.create_time < 1580486400) THENpg_9_db$# INSERT INTO pg_9_tab_p_202001 VALUES (NEW.*); pg_9_db$# ELSEpg_9_db$# RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger () Functionalizers and pgrub9 END IF;pg_9_db$# RETURN NULL;pg_9_db$# END;pg_9_db$# $function$;CREATE FUNCTIONpg_9_db=# pg_9_db=# ALTER TABLE pg_9_tab_p_202001 INHERIT pg_9_tab ALTER TABLEpg_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_hisotrypg_9_db=#-delete partition 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_hisotrypg_9_db=# pg_9_db=# ALTER TABLE pg_9_tab_p_hisotry NO INHERIT pg_9_tab ALTER TABLEpg_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_202001pg_9_db=# DROP TABLE pg_9_tab_p_hisotry DROP TABLEpg_9_db=#
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.