Postgresql
在 Postgres 11.5 上的分區表上觸發
我問了一個關於PG 11.5 中刪除的歷史表設計的問題，並收到了對錶進行分區的建議。這是一個絕妙的主意，因為表格可能會變得很大，而且資訊量很低。意思是，我最終會想要清除數據。
當我用分區重新實現表時，我發現 PG（11 和 12）不支持
BEFORE ROW主分區表上的觸發器，只支持單個分區上的觸發器。這導致了大量的綁定程式碼。有沒有更好的辦法？在這種情況下，我得到的觸發器就是減去兩個時間戳並儲存秒數。11.5，所以沒有生成的列。即使程式碼很長，我也將其包含在內，因為這就是重點。
Column order tweaked a bit with Column Tetris search from https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/ Totally geeky, but this table could get big, so its worth saving some room. Note that we can also roll up data and discard a lot of the details in this table, if we want to save room. */ BEGIN; DROP TABLE IF EXISTS data.need_history CASCADE; CREATE TABLE IF NOT EXISTS data.need_history ( id uuid NOT NULL DEFAULT NULL, item_id uuid NOT NULL DEFAULT NULL, facility_id uuid NOT NULL DEFAULT NULL, hsys_id uuid NOT NULL DEFAULT NULL, perc_down double precision NOT NULL DEFAULT 0, created_dts timestamptz NOT NULL DEFAULT NULL, deleted_dts timestamptz NOT NULL DEFAULT NOW(), total_qty integer NOT NULL DEFAULT 0, sterile_qty integer NOT NULL DEFAULT 0, available_qty integer NOT NULL DEFAULT 0, still_need_qty integer NOT NULL DEFAULT 0, usage_ integer NOT NULL DEFAULT 0, duration_seconds int4 NOT NULL DEFAULT 0, need_for_case citext NOT NULL DEFAULT NULL, status citext NOT NULL DEFAULT NULL, CONSTRAINT need_history_id_pkey PRIMARY KEY (id,deleted_dts) ) PARTITION BY RANGE (deleted_dts); ALTER TABLE data.need_history OWNER TO user_change_structure; /* It's a big confusingly documented, but ranges are *inclusive* FROM and *exclusive* TO. So, to get January, you want 01-01 to 02-01, not 01-01 to 01-31. In practice, this makes the range descriptions a bit nicer, I'd say. */ CREATE TABLE ascendco.need_history_2019_11 PARTITION OF need_history FOR VALUES FROM ('2019-11-01') TO ('2019-12-01'); CREATE TABLE ascendco.need_history_2019_12 PARTITION OF need_history FOR VALUES FROM ('2019-12-01') TO ('2020-01-01'); CREATE TABLE ascendco.need_history_2020_01 PARTITION OF need_history FOR VALUES FROM ('2020-01-01') TO ('2020-02-01'); CREATE TABLE ascendco.need_history_2020_02 PARTITION OF need_history FOR VALUES FROM ('2020-02-01') TO ('2020-03-01'); CREATE TABLE ascendco.need_history_2020_03 PARTITION OF need_history FOR VALUES FROM ('2020-03-01') TO ('2020-04-01'); CREATE TABLE ascendco.need_history_2020_04 PARTITION OF need_history FOR VALUES FROM ('2020-04-01') TO ('2020-05-01'); CREATE TABLE ascendco.need_history_2020_05 PARTITION OF need_history FOR VALUES FROM ('2020-05-01') TO ('2020-06-01'); CREATE TABLE ascendco.need_history_2020_06 PARTITION OF need_history FOR VALUES FROM ('2020-06-01') TO ('2020-07-01'); CREATE TABLE ascendco.need_history_2020_07 PARTITION OF need_history FOR VALUES FROM ('2020-07-01') TO ('2020-08-01'); CREATE TABLE ascendco.need_history_2020_08 PARTITION OF need_history FOR VALUES FROM ('2020-08-01') TO ('2020-09-01'); CREATE TABLE ascendco.need_history_2020_09 PARTITION OF need_history FOR VALUES FROM ('2020-09-01') TO ('2020-10-01'); CREATE TABLE ascendco.need_history_2020_10 PARTITION OF need_history FOR VALUES FROM ('2020-10-01') TO ('2020-11-01'); CREATE TABLE ascendco.need_history_2020_11 PARTITION OF need_history FOR VALUES FROM ('2020-11-01') TO ('2020-12-01'); CREATE TABLE ascendco.need_history_2020_12 PARTITION OF need_history FOR VALUES FROM ('2020-12-01') TO ('2021-01-01'); CREATE TABLE ascendco.need_history_default PARTITION OF need_history DEFAULT; COMMIT; /* Define the trigger function to update the duration count. In PG 12 well be able to do this with a generated column...easier. */ CREATE OR REPLACE FUNCTION data.need_history_insert_trigger() RETURNS trigger AS $BODY$ BEGIN /* Use DATE_TRUNC seconds to get just the whole seconds part of the timestamps. */ NEW.duration_seconds = EXTRACT(EPOCH FROM ( DATE_TRUNC('second', NEW.deleted_dts) - DATE_TRUNC('second', NEW.created_dts) )); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; /* Bind a trigger event to the function. Note: In PG 11 & 12, BEFORE ROW triggers must be applied to the individual partitions, not the partition table. */ DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2019_11 ON data.need_history_2019_11; CREATE TRIGGER trigger_need_history_before_insert_2019_11 BEFORE INSERT ON data.need_history_2019_11 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2019_12 ON data.need_history_2019_12; CREATE TRIGGER trigger_need_history_before_insert_2019_12 BEFORE INSERT ON data.need_history_2019_12 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_01 ON data.need_history_2020_01; CREATE TRIGGER trigger_need_history_before_insert_2020_01 BEFORE INSERT ON data.need_history_2020_01 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_02 ON data.need_history_2020_02; CREATE TRIGGER trigger_need_history_before_insert_2020_02 BEFORE INSERT ON data.need_history_2020_02 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_03 ON data.need_history_2020_03; CREATE TRIGGER trigger_need_history_before_insert_2020_03 BEFORE INSERT ON data.need_history_2020_03 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_04 ON data.need_history_2020_04; CREATE TRIGGER trigger_need_history_before_insert_2020_04 BEFORE INSERT ON data.need_history_2020_04 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_05 ON data.need_history_2020_05; CREATE TRIGGER trigger_need_history_before_insert_2020_05 BEFORE INSERT ON data.need_history_2020_05 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_06 ON data.need_history_2020_06; CREATE TRIGGER trigger_need_history_before_insert_2020_06 BEFORE INSERT ON data.need_history_2020_06 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_07 ON data.need_history_2020_07; CREATE TRIGGER trigger_need_history_before_insert_2020_07 BEFORE INSERT ON data.need_history_2020_07 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_08 ON data.need_history_2020_08; CREATE TRIGGER trigger_need_history_before_insert_2020_08 BEFORE INSERT ON data.need_history_2020_08 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_09 ON data.need_history_2020_09; CREATE TRIGGER trigger_need_history_before_insert_2020_09 BEFORE INSERT ON data.need_history_2020_09 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_10 ON data.need_history_2020_10; CREATE TRIGGER trigger_need_history_before_insert_2020_10 BEFORE INSERT ON data.need_history_2020_10 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_11 ON data.need_history_2020_11; CREATE TRIGGER trigger_need_history_before_insert_2020_11 BEFORE INSERT ON data.need_history_2020_11 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_12 ON data.need_history_2020_12; CREATE TRIGGER trigger_need_history_before_insert_2020_12 BEFORE INSERT ON data.need_history_2020_12 FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger(); DROP TRIGGER IF EXISTS trigger_need_history_before_insert_default ON data.need_history_default; CREATE TRIGGER trigger_need_history_before_insert_default BEFORE INSERT ON data.need_history_default FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();``` [1]: https://dba.stackexchange.com/questions/253891/history-table-design-for-deletions-in-pg-11-5
我不知道對此有任何內置解決方案；
CREATE TRIGGER我認為您最終需要為每個新分區執行自己的語句。有幾種方法可以自動執行此操作。這個功能會派上用場：
CREATE FUNCTION add_trigger(partition_id regclass) RETURNS VOID AS $$ DECLARE partition_name TEXT; BEGIN partition_name = (SELECT relname FROM pg_class WHERE oid = partition_id); EXECUTE format( $SQL$ CREATE TRIGGER %I BEFORE INSERT ON %s FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger() $SQL$, partition_name || '_before_insert', partition_id::text ); END $$ LANGUAGE plpgsql;如果你：
- 確實需要
CREATE TABLE語句自動添加觸發器，並且- 有權訪問超級使用者角色
…然後您可以編寫一個事件觸發器以在創建分區時觸發並自動安裝觸發器：
CREATE FUNCTION add_trigger_to_new_partition() RETURNS event_trigger AS $$ BEGIN PERFORM add_trigger(command.objid::regclass) FROM pg_event_trigger_ddl_commands() command JOIN pg_inherits ON inhrelid = command.objid AND inhparent = 'data.need_history'::regclass; END; $$ LANGUAGE plpgsql; CREATE EVENT TRIGGER add_trigger_to_new_partition ON ddl_command_end WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION add_trigger_to_new_partition();但是，在大多數情況下，將整個分區創建過程包裝在一個函式中可能更簡單：
CREATE FUNCTION create_new_partition(start_of_month DATE) RETURNS void AS $$ DECLARE partition_name TEXT; BEGIN ASSERT extract(day from start_of_month) = 1; partition_name = 'data.need_history_' || to_char(start_of_month, 'YYYY_MM'); EXECUTE format( $SQL$ CREATE TABLE %s PARTITION OF need_history FOR VALUES FROM (%L) TO (%L); $SQL$, partition_name, start_of_month, start_of_month + interval '1 month' ); PERFORM add_trigger(partition_name::regclass); END $$ LANGUAGE plpgsql;無論觸發要求如何，這可能都是值得的：它很好地封裝了分區方案的細節，確保正確建構分區範圍並遵循命名約定，以及使添加分區的整個過程更加使用者化友好。例如，可以使用單個命令創建腳本中的分區：
SELECT create_new_partition(start_of_month::date) FROM generate_series('2019-11-01'::date, '2020-12-01'::date, '1 month') start_of_month綜上所述，如果您的歷史記錄表是
INSERT-only，並且新記錄的唯一來源是語句 trigger ondata.need，那麼我可能不會遇到所有這些麻煩；我只是計算語句本身的duration_seconds值。INSERT為了偏執，您還可以添加一個
CHECK約束data.need_history（將由所有分區繼承）以驗證該欄位是否設置正確：CHECK( duration_seconds * interval '1 second' = DATE_TRUNC('second', deleted_dts) - DATE_TRUNC('second', created_dts) )