パーティション分割についての会話を続けます。 前回は、毎日パーティション分割されたテーブルの単純なタスク、いわゆる範囲パーティション分割について見てきました。 テーブルをセクションに分割したところ、パーティションテーブルのサポートという新しい問題が発生しました。 時間内に新しいセクションを作成し、古いセクションをアーカイブし、それに応じて新しいデータの挿入を制御するトリガーを変更する必要があります。
私はそのとき私は少しunningだったと言わなければなりません）。 簡単な解決策はまだありません。誰もが自分の自転車を再発明しなければなりません。 postgresqlの将来のバージョンでは、パーティション化されたテーブルを作成するための構文が確実に表示されます（私の知る限り、このようなパッチは既に存在します）。
今日は、パーティション分割タスクを簡素化するために使用するソリューションを共有したいと思います。 トピックの最初の部分と同じ問題を検討します。
分割タスクの自動化
タスクをある程度自動化するには、いくつかの補助オブジェクトを作成する必要があります。
セクション表
この表では、セクションを保存します。 master_tableは（スキーマと一緒に）メインテーブルの名前、partition_tableは（スキーマと一緒に）セクションの名前、range_checkはこのセクションの制限です。
CREATE TABLE public .table_partitions
(
master_table text NOT NULL ,
partition_table text NOT NULL ,
range_check text NOT NULL ,
time_added TIMESTAMP DEFAULT now() NOT NULL ,
CONSTRAINT table_partitions_primary_key PRIMARY KEY (master_table, partition_table)
);
* This source code was highlighted with Source Code Highlighter .
新しいセクションを追加する機能
この関数を使用すると、新しいセクションを追加できます。 トリガーへの挿入条件は自動的に変更されます。
CREATE OR REPLACE FUNCTION public .pg_add_range_partition( IN p_master_table text,
IN p_partition_table text, IN p_range_check text, IN p_trigger_function text,
OUT status_code text)
RETURNS text AS
$$
DECLARE
v_table_ddl text := 'CREATE TABLE [PARTITION_TABLE] ( CHECK ( [RANGE_CHECK] ) ) INHERITS ([MASTER_TABLE]);' ;
v_trigger_ddl text := 'CREATE OR REPLACE FUNCTION [TRIGGER_FUNCTION]() RETURNS TRIGGER AS $body$ ' ||
'BEGIN [RANGE_CHECKS] ELSE RAISE EXCEPTION ' ||
'' 'Inserted data is out of range. Fix [TRIGGER_FUNCTION].' '; ' ||
'END IF; RETURN NULL; END; $body$ LANGUAGE plpgsql;' ;
v_range_checks text := '' ;
rec record;
BEGIN
IF EXISTS ( SELECT 1 FROM public .table_partitions
WHERE master_table = p_master_table
AND partition_table = p_partition_table) THEN
status_code := 'Partition ' || p_partition_table || ' already exists' ;
RETURN ;
END IF ;
v_table_ddl := replace(v_table_ddl, '[PARTITION_TABLE]' , p_partition_table);
v_table_ddl := replace(v_table_ddl, '[RANGE_CHECK]' , p_range_check);
v_table_ddl := replace(v_table_ddl, '[MASTER_TABLE]' , p_master_table);
FOR rec IN ( SELECT 'ELSIF (' || tp.range_check || ') THEN INSERT INTO ' ||
tp.partition_table || ' VALUES (NEW.*); ' AS range_check
FROM public .table_partitions tp
WHERE tp.master_table = p_master_table
ORDER BY tp.time_added DESC ) LOOP
v_range_checks := _pg_check_to_trigger(p_master_table, rec.range_check) || v_range_checks;
END LOOP;
v_range_checks := 'IF (' || _pg_check_to_trigger(p_master_table, p_range_check) ||
') THEN INSERT INTO ' || p_partition_table ||
' VALUES (NEW.*); ' || v_range_checks;
v_trigger_ddl := replace(v_trigger_ddl, '[TRIGGER_FUNCTION]' , p_trigger_function);
v_trigger_ddl := replace(v_trigger_ddl, '[RANGE_CHECKS]' , v_range_checks);
RAISE NOTICE 'Partition script: %' , v_table_ddl;
RAISE NOTICE 'Trigger script: %' , v_trigger_ddl;
EXECUTE v_table_ddl;
EXECUTE v_trigger_ddl;
INSERT INTO public .table_partitions (master_table, partition_table, range_check)
VALUES (p_master_table, p_partition_table, p_range_check);
status_code := 'OK' ;
RETURN ;
EXCEPTION
WHEN OTHERS THEN
status_code := 'Unexpected error: ' || SQLERRM;
END ;
$$ LANGUAGE 'plpgsql' ;
CREATE OR REPLACE FUNCTION public ._pg_check_to_trigger( IN master_table text, IN range_check text)
RETURNS text AS
$$
DECLARE
v_schema text := COALESCE ( SUBSTRING (master_table FROM E '(.*)\\.' ), 'public' );
v_tablename text := replace(master_table, v_schema || '.' , '' );
v_range_check text := range_check;
rec record;
BEGIN
RAISE NOTICE '%' , v_schema;
RAISE NOTICE '%' , v_tablename;
FOR rec IN ( SELECT column_name
FROM information_schema.columns
WHERE table_schema = v_schema
AND table_name = v_tablename) LOOP
v_range_check := replace(v_range_check, rec.column_name, 'NEW.' || rec.column_name);
END LOOP;
RETURN v_range_check;
END ;
$$ LANGUAGE 'plpgsql' ;
* This source code was highlighted with Source Code Highlighter .
例
1.最初のステップは、マスターテーブルとトリガーを作成することです。
CREATE TABLE analytics.events
(
event_id BIGINT DEFAULT nextval( 'analytics.seq_events' ) PRIMARY KEY ,
user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);
CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS
$body$
BEGIN
RETURN NULL ;
END ; $body$ LANGUAGE plpgsql;
CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();
* This source code was highlighted with Source Code Highlighter .
2.セクションを追加する
SELECT *
FROM pg_add_range_partition( 'analytics.events' ,
'analytics.events_01012010' ,
'event_time >= TIMESTAMP ' '2010-01-01 00:00:00' ' AND event_time < TIMESTAMP ' '2010-01-02 00:00:00' '' ,
'analytics.events_insert_trigger' );
* This source code was highlighted with Source Code Highlighter .
3.パーティションテーブルを取得したら、作業を開始できます。
INSERT INTO analytics.events (user_id, event_type_id, event_time, url, referrer, ip)
VALUES ( '550e8400-e29b-41d4-a716-446655440000' ::UUID, 1, '2010-01-01 15:01:01' :: TIMESTAMP ,
'http://aymeshkov.habrahabr.ru' , 'http://habrahabr.ru' , '127.0.0.1' ::INET);
* This source code was highlighted with Source Code Highlighter .
あとがき
1.もちろん、私が提案する解決策はすべての人に適しているわけではありませんが、特定のタスクのためにそれを完成させることはそれほど難しくないと思います。
2.多くの人にとって生活を楽にする小さな注意点の1つは、generate_series（）を使用して、まだ作成する必要があるセクションを見つけることです。
SELECT '2010-01-01' :: DATE + num * '1 day' :: INTERVAL AS day
FROM generate_series(0, 29) num
* This source code was highlighted with Source Code Highlighter .
第二部の終わり
主に大量のコードが原因で、このトピックは膨大になりました。 繰り返しますが、私が適合について話したかったすべてのことではありません。 次のトピックは、第3部に残ります。
1.ルールによるセクション管理-どのように行われ、なぜ悪いのか。
2.毎回トリガーに悩まされたくないですか？ しないでください-LIST PARTITIONINGを使用してください。