主页 / dba / 问题 / 253973
Accepted
Morris de Oryx
Asked: 2019-11-23 18:54:31 +0800 CST

在 Postgres 11.5 上的分区表上触发

  • 772

我问了一个关于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
postgresql trigger

1 个回答

  1. Best Answer

    我不知道对此有任何内置解决方案；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 on data.need，那么我可能不会遇到所有这些麻烦；我只是计算语句本身的duration_seconds值。INSERT

    为了偏执，您还可以添加一个CHECK约束data.need_history（将由所有分区继承）以验证该字段是否设置正确：

    CHECK(
  duration_seconds * interval '1 second' = 
    DATE_TRUNC('second', deleted_dts) - DATE_TRUNC('second', created_dts)
 )
    • 4

相关问题