👨‍🏭 ♊️ 👉🏽 セクショニング：ショットとフォーゲット 👽 🧚🏼 🎅🏽

セクショニングに関する多くの情報を見つけることができます。特に、理論について読むことができます。その後、著者はアイデアを開発し、セクションをすばやく追加するためのソリューションを提供します。よく理解しておくことをお勧めします。

理論を研究した後、ほとんど全員がセクションの作成プロセスを自動化するというアイデアを思いつきます。上記は選択肢の1つであり、尊敬されているクリエイターから私が見た2番目の複雑な選択肢は、私だけでなくZabbixだと思います。

少し適応した後、自宅で紹介することにしました...残念なことに、いくつかの欠点が明らかになりました。新しいセクションを作成すると、このセクションの最初のレコードが失われました。セクションが多数ある場合、1つのレコードの挿入にも時間がかかりすぎます（2つの要因によります：レコードを配置する場所にテーブルが計算されるたびに、すべての条件で1つのトリガーではなく多くのルールを使用します）。それでも、彼らは素晴らしい仕事をしてくれたので、私はこの機会に敬意を表します。

その結果、私はあなたの判決を下します。開始するには、パーティション分割を開始する方法の例：

期間：

select create_partition('partitions.test1.cdate', 'date', 'day,YYYY_MM_DD', now()::text); select create_partition('partitions.test2.cdate', 'date', 'week,YYYY_IW', now()::text); select create_partition('partitions.test3.cdate', 'date', 'month,YYYY_MM', now()::text);

これらのコマンドは、テーブルtest1、test2、test3の日、週、月ごとにパーティション分割を開始します

パラメーターを分析しましょう。

最初のフィールド「 partitions.test1.cdate 」の値はドットで区切られています：

パーティション -セクションが追加されるスキームの名前

test1-現在のスコープの祖先テーブル

cdate-テーブルを分割するフィールド

2番目のフィールド「日付」はセクションのタイプを設定します

3番目のフィールド「day、YYYY_MM_DD」は、セクショニングのパラメーターを設定します

day-毎日セクションを作成します

YYYY_MM_DD-テーブルの接尾辞

そして最後に、4番目のnow（）::セクションを作成するためのテキストのサンプルデータ

別の例：

 select create_partition('partitions.test4.id', 'digits', '10,2', 1::int);

ここで、左にゼロを追加することにより（必要な場合）、数値は10ビットに削減され、8番目の左のすべての数字がセクション名に使用されます。

1,000,000,000は、partitions.test4_10セクションに分類されます

セクションpartitions.test4_01に100000000

セクションパーティションあたり10000000。test4_00

10000000000からセクションpartitions.test4_100

仕組みを簡単に説明します。

select create_partition（ 'partitions.test1.cdate'、 'date'、 'day、YYYY_MM_DD'、now（）:: text）; 最初に、「プラグイン」のpartition_ dateが呼び出されます。この関数の目的は、渡されたパラメーターに従ってセクションの名前とセクションに入る条件を返すことです。さらにメイン関数では、現在のテーブルから必要な条件を継承して新しいテーブルが作成され、すべての祖先インデックスがコピーされます。

次に、システムテーブルからのクエリは、子孫テーブルに関するデータとこのデータからのチェック条件を引き出し、データをセクションに分散するトリガーを作成します。

これで、挿入を行うと、トリガーが起動され、目的のテーブルセクションが決定されます。そのようなテーブルがない場合、セクションを作成したときと同じパラメーターでcreate_partitionが呼び出されます。

最初の例では、1年の充填後、トリガーは次のようになります。

 CREATE OR REPLACE FUNCTION trig_partition_test1_cdate() RETURNS TRIGGER AS $BODY2$ declare child text; begin IF ((NEW.cdate >= '2012-09-01 00:00:00-07'::timestamp with time zone) AND (NEW.cdate < '2012-10-01 00:00:00-07'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_09 VALUES (NEW.*); ELSIF ((NEW.cdate >= '2012-08-01 00:00:00-07'::timestamp with time zone) AND (NEW.cdate < '2012-09-01 00:00:00-07'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_08 VALUES (NEW.*); ELSIF ((NEW.cdate >= '2012-07-01 00:00:00-07'::timestamp with time zone) AND (NEW.cdate < '2012-08-01 00:00:00-07'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_07 VALUES (NEW.*); ELSIF ((NEW.cdate >= '2012-06-01 00:00:00-07'::timestamp with time zone) AND (NEW.cdate < '2012-07-01 00:00:00-07'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_06 VALUES (NEW.*); ELSIF ((NEW.cdate >= '2012-05-01 00:00:00-07'::timestamp with time zone) AND (NEW.cdate < '2012-06-01 00:00:00-07'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_05 VALUES (NEW.*); ELSIF ((NEW.cdate >= '2012-04-01 00:00:00-07'::timestamp with time zone) AND (NEW.cdate < '2012-05-01 00:00:00-07'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_04 VALUES (NEW.*); ELSIF ((NEW.cdate >= '2012-03-01 00:00:00-08'::timestamp with time zone) AND (NEW.cdate < '2012-04-01 00:00:00-07'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_03 VALUES (NEW.*); ELSIF ((NEW.cdate >= '2012-02-01 00:00:00-08'::timestamp with time zone) AND (NEW.cdate < '2012-03-01 00:00:00-08'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_02 VALUES (NEW.*); ELSIF ((NEW.cdate >= '2012-01-01 00:00:00-08'::timestamp with time zone) AND (NEW.cdate < '2012-02-01 00:00:00-08'::timestamp with time zone)) THEN INSERT INTO public.test1_2012_01 VALUES (NEW.*); ELSE EXECUTE 'SELECT create_partition(''public.test1.cdate''::text, ''date''::text, ''month,YYYY_MM''::text , '''||NEW.cdate||'''::text)' INTO child; EXECUTE 'INSERT INTO '|| child || ' SELECT $1.*' USING NEW; END IF; RETURN NULL; end; $BODY$ LANGUAGE 'plpgsql';

あなたが見ることができるように、それはドキュメントからの実際に古典的な例であることがわかります:)

ご想像のとおり、このソリューションは、たとえば、文字列の最初の文字または文字列のハッシュで分割することにより、簡単に拡張できます。この「プラグイン」を自分で作成し、コメントに投稿することをお勧めします。

誰かが私のソリューションが役立つと思うことを願っています

sql.ruフォーラムにあるヘルパースクリプト

 CREATE OR REPLACE FUNCTION to_timestamp(timestamp without time zone) RETURNS timestamp with time zone AS $_$ select $1::timestamp with time zone; $_$ LANGUAGE sql STABLE STRICT; CREATE OR REPLACE FUNCTION to_timestamp(timestamp with time zone) RETURNS timestamp with time zone AS $_$ select $1::timestamp with time zone; $_$ LANGUAGE sql STABLE STRICT; CREATE OR REPLACE FUNCTION to_numeric(text) RETURNS numeric AS $_$ select regexp_replace($1, '\D+', '', 'g')::numeric; $_$ LANGUAGE sql STABLE STRICT; CREATE OR REPLACE FUNCTION copy_constraints(srcoid oid, dstoid oid) RETURNS integer AS $BODY$ declare i int4 := 0; constrs record; srctable text; dsttable text; begin srctable = srcoid::regclass; dsttable = dstoid::regclass; for constrs in select conname as name, pg_get_constraintdef(oid) as definition from pg_constraint where conrelid = srcoid loop begin execute 'alter table ' || dsttable || ' add constraint ' || replace(replace(constrs.name, srctable, dsttable),'.','_') || ' ' || constrs.definition; i = i + 1; exception when duplicate_table then end; end loop; return i; exception when undefined_table then return null; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION copy_constraints(src text, dst text) RETURNS integer AS $BODY$ begin return copy_constraints(src::regclass::oid, dst::regclass::oid); end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; CREATE OR REPLACE FUNCTION copy_indexes(srcoid oid, dstoid oid) RETURNS integer AS $BODY$ declare i int4 := 0; indexes record; srctable text; dsttable text; script text; begin srctable = srcoid::regclass; dsttable = dstoid::regclass; for indexes in select c.relname as name, pg_get_indexdef(idx.indexrelid) as definition from pg_index idx, pg_class c where idx.indrelid = srcoid and c.oid = idx.indexrelid loop script = replace (indexes.definition, ' INDEX ' || indexes.name, ' INDEX ' || replace(replace(indexes.name, srctable, dsttable),'.','_')); script = replace (script, ' ON ' || srctable, ' ON ' || dsttable); begin execute script; i = i + 1; exception when duplicate_table then end; end loop; return i; exception when undefined_table then return null; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION copy_indexes(src text, dst text) RETURNS integer AS $BODY$ begin return copy_indexes(src::regclass::oid, dst::regclass::oid); end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; CREATE OR REPLACE FUNCTION copy_triggers(srcoid oid, dstoid oid) RETURNS integer AS $BODY$ declare i int4 := 0; triggers record; srctable text; dsttable text; script text = ''; begin srctable = srcoid::regclass; dsttable = dstoid::regclass; for triggers in select tgname as name, pg_get_triggerdef(oid) as definition from pg_trigger where tgrelid = srcoid loop script = replace (triggers.definition, ' TRIGGER ' || triggers.name, ' TRIGGER ' || replace(replace(triggers.name, srctable, dsttable),'.','_')); script = replace (script, ' ON ' || srctable, ' ON ' || dsttable); begin execute script; i = i + 1; exception when duplicate_table then end; end loop; return i; exception when undefined_table then return null; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION copy_triggers(src text, dst text) RETURNS integer AS $BODY$ begin return copy_triggers(src::regclass::oid, dst::regclass::oid); end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE;

主なスクリプト

 CREATE OR REPLACE FUNCTION "create_partition" (in tbl varchar, in method varchar, in params varchar, in sample text) RETURNS text AS $BODY$ declare scheme varchar := split_part(tbl, '.', 1); parent varchar := split_part(tbl, '.', 2); field varchar := split_part(tbl, '.', 3); child varchar; script text; trig text; part text[]; begin execute 'select partition_'||method||'('''||params||''', '''||field||''', '''||sample||''')' into part; -- RAISE EXCEPTION 'part %', part; child = scheme || '.' || parent || '_' || (part[1]::text); execute 'create table IF NOT EXISTS ' || child || ' ( constraint partition_' || (part[1]) || ' check ' || (part[2]) || ' ) inherits (' || parent || ')'; perform copy_constraints(parent, child); perform copy_indexes(parent, child); -- execute 'GRANT SELECT ON ' || child || ' TO some_other_user'; -- execute 'GRANT ALL ON ' || child || ' TO user'; script = (select string_agg(c, chr(10)||' ELS') from ( select to_numeric(replace(t.table_name, parent||'_','')) as n, 'IF '||replace(left(right(cc.check_clause, -1), -1), c.column_name, 'NEW.'||c.column_name)||' THEN INSERT INTO '||t.table_schema||'.'||t.table_name||' VALUES (NEW.*);' as c from information_schema.TABLE_CONSTRAINTS t join information_schema.CONSTRAINT_COLUMN_USAGE c ON t.constraint_name = c.constraint_name join information_schema.check_constraints cc ON t.constraint_name = cc.constraint_name where constraint_type IN ('CHECK') and t.table_name like parent||'\_%' group by t.table_schema, t.table_name, c.column_name, cc.check_clause order by n desc) t); trig = 'trig_partition_'||parent||'_'||field; execute 'CREATE OR REPLACE FUNCTION '||trig||'() RETURNS TRIGGER AS $BODY2$ declare child text; begin '||script||' ELSE EXECUTE ''SELECT create_partition('''''||tbl||'''''::text, '''''||method||'''''::text, '''''||params||'''''::text , ''''''||NEW.'||field||'||''''''::text)'' INTO child; EXECUTE ''INSERT INTO ''|| child || '' SELECT $1.*'' USING NEW; END IF; RETURN NULL; end; $BODY2$ LANGUAGE ''plpgsql'' VOLATILE;'; execute 'DROP TRIGGER IF EXISTS t_'||trig||' ON "'||parent||'" CASCADE'; execute 'CREATE TRIGGER t_'||trig||' BEFORE INSERT ON "'||parent||'" FOR EACH ROW EXECUTE PROCEDURE '||trig||'();'; return child; end; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION "partition_date" (in params text, in field varchar, in sample timestamp with time zone) RETURNS text[] AS $BODY$ declare period varchar:= split_part(params, ',', 1); fmt varchar := split_part(params, ',', 2); clock timestamp with time zone := to_timestamp(sample); delta varchar := '1 '||period; suffix varchar; check_beg varchar; check_end varchar; condition varchar; begin -- RAISE EXCEPTION 'period %, fmt %, clock %', period, fmt, clock; check_beg = date_trunc(period, clock); check_end = date_trunc(period, clock + delta::interval); suffix = to_char (clock, fmt); condition = '( ' || field || ' >= ' || quote_literal (check_beg) || ' and ' || field || ' < ' || quote_literal (check_end) || ' )'; return ARRAY[suffix, condition]; end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; CREATE OR REPLACE FUNCTION "partition_digits" (in params text, in field varchar, in sample numeric) RETURNS text[] AS $BODY$ declare len int := split_part(params, ',', 1)::int; pref int := split_part(params, ',', 2)::int; norm text := to_char(sample::numeric, 'FM000000000000000000000'); suffix text := regexp_replace(left(norm, -1*(len-pref)), '0*(?=\d{'||pref::text||'})', ''); check_beg varchar; check_end varchar; condition varchar; begin check_beg = (trunc(norm::numeric, -1*(len-pref)))::numeric::text; check_end = (check_beg::numeric+10^(len-pref))::numeric::text; condition = '( ' || field || ' >= ' || check_beg || ' and ' || field || ' < ' || check_end || ' )'; return ARRAY[suffix, condition]; end; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE;

テスト

日付セクション

 DROP TABLE IF EXISTS "public"."test1" CASCADE; CREATE TABLE "public"."test1" ( "id" serial, "cdate" timestamp with time zone, "text" text, CONSTRAINT "test11_pkey" PRIMARY KEY (id) ) WITH OIDS; CREATE INDEX test_idx_cdate ON test1 USING btree (cdate); -- select create_partition('public.test1.cdate', 'date', 'day,YYYY_MM_DD', now()::text); -- select create_partition('public.test1.cdate', 'date', 'week,YYYY_IW', now()::text); select create_partition('public.test1.cdate', 'date', 'month,YYYY_MM', now()::text); -- insert into test1 (cdate, text) SELECT cdate, md5(random()::text) as text FROM generate_series('2012-01-01'::date, now(), '1 day'::interval) cdate;

番号範囲セクション：

 DROP TABLE IF EXISTS "public"."test2" CASCADE; CREATE TABLE "public"."test2" ( "id" bigserial, "text" text, CONSTRAINT "test2_pkey" PRIMARY KEY (id) ) WITH OIDS; select create_partition('public.test2.id', 'digits', '10,2', 1::int); insert into test2 values(10000000, 'test2'); -- insert into test2 (id, text) SELECT t.id, md5(random()::text) as text FROM generate_series(0, 100000000000, 1000000000) as t(id) limit 200;

セクショニング：ショットとフォーゲット

More articles: