实例
drop table if exists tab cascade;
CREATE TABLE tab (
id bigint GENERATED ALWAYS AS IDENTITY,
ts timestamp NOT NULL,
data text
);
CREATE or replace FUNCTION part_trig() RETURNS trigger
LANGUAGE plpgsql AS
$$
declare
sql_cmd varchar='';
BEGIN
BEGIN
sql_cmd= format(
'CREATE TABLE %1$s (LIKE tab INCLUDING INDEXES)',
'tab_' || to_char(NEW.ts, 'YYYY_MM_DD')
);
-- raise notice 'sql_cmd=%',sql_cmd;
EXECUTE sql_cmd;
EXECUTE
format(
'NOTIFY tab, %L',
to_char(NEW.ts, 'YYYY-MM-DD')
);
EXCEPTION
WHEN duplicate_table THEN
NULL; -- ignore
END;
EXECUTE
format(
'INSERT INTO %I VALUES ($1.*)',
'tab_' || to_char(NEW.ts, 'YYYY_MM_DD')
)
USING NEW;
RETURN NULL;
END;$$;
CREATE TRIGGER part_trig
BEFORE INSERT ON TAB FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE FUNCTION part_trig();
测试用例
\timing on
INSERT INTO tab (ts, data)
SELECT clock_timestamp(), 'something'
FROM generate_series(1, 100000);
\dt
ALTER TABLE tab DISABLE TRIGGER part_trig;
truncate tab;
INSERT INTO tab (ts, data)
SELECT clock_timestamp(), 'something'
FROM generate_series(1, 100000);
实操
postgres=# \timing on
Timing is on.
postgres=# INSERT INTO tab (ts, data)
postgres-# SELECT clock_timestamp(), 'something'
postgres-# FROM generate_series(1, 100000);
\dt
ALTER TABLE tab DISABLE TRIGGER part_trig;
truncate tab;
INSERT INTO tab (ts, data)
SELECT clock_timestamp(), 'something'
FROM generate_series(1, 100000);INSERT 0 0
Time: 4784.318 ms (00:04.784)
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | tab | table | postgres
public | tab_2022_08_28 | table | postgres
(2 rows)
postgres=#
postgres=#
postgres=# ALTER TABLE tab DISABLE TRIGGER part_trig;
ALTER TABLE
Time: 1.429 ms
postgres=# truncate tab;
TRUNCATE TABLE
Time: 23.540 ms
postgres=# INSERT INTO tab (ts, data)
postgres-# SELECT clock_timestamp(), 'something'
postgres-# FROM generate_series(1, 100000);
INSERT 0 100000
Time: 225.612 ms
postgres=#
