Início / dba / Perguntas / 253973
Accepted
Morris de Oryx
Asked: 2019-11-23 18:54:31 +0800 CST

Gatilhos em tabelas particionadas no Postgres 11.5

  • 772

Fiz uma pergunta sobre o design da tabela de histórico para exclusões no PG 11.5 e recebi uma sugestão para particionar a tabela. Essa é uma excelente ideia, pois a tabela pode ficar enorme e o conteúdo de informações é baixo. Ou seja, vou acabar querendo limpar os dados.

Quando fui reimplementar a tabela com partição, descobri que o PG (11 e 12) não suporta BEFORE ROWtriggers na tabela de partição master, apenas na partição individual. O que leva a uma tonelada de código de ligação. Existe uma maneira melhor? Tudo que eu tenho o gatilho neste caso é subtrair dois timestamps e armazenar os segundos. 11.5, portanto, nenhuma coluna gerada.

Estou incluído no código, embora seja bastante longo, pois esse é o ponto.

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 respostas

  1. Best Answer

    Não conheço nenhuma solução integrada para isso; Acho que você precisa executar sua própria CREATE TRIGGERinstrução para cada nova partição.

    Existem algumas maneiras de automatizar isso. Esta função será útil:

    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;

    Se você:

    • Realmente precisa da CREATE TABLEinstrução para adicionar o gatilho automaticamente e
    • Ter acesso a uma função de superusuário

    ...então você pode escrever um gatilho de evento para disparar na criação da partição e instalar o gatilho automaticamente:

    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();

    No entanto, na maioria dos casos, provavelmente é mais simples envolver todo o processo de criação de partição em uma função:

    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;

    Isso pode valer a pena fazer independentemente do requisito do gatilho: ele mantém os detalhes do seu esquema de particionamento bem encapsulados, garante que os intervalos de partição sejam construídos corretamente e as convenções de nomenclatura sejam seguidas, além de tornar todo o processo de adição de partições muito mais fácil de usar. amigáveis. Por exemplo, as partições em seu script podem ser criadas com um único comando:

    SELECT create_new_partition(start_of_month::date)
FROM generate_series('2019-11-01'::date, '2020-12-01'::date, '1 month') start_of_month

    Dito tudo isso, se suas tabelas de histórico são INSERT-only, e a única fonte de novos registros é a instrução trigger on data.need, então eu provavelmente não teria todo esse problema; Eu apenas calcularia o duration_secondsvalor na INSERTprópria declaração.

    Para ficar paranóico, você também pode adicionar uma CHECKrestrição em data.need_history(que será herdada por todas as partições) para verificar se o campo está sendo definido corretamente:

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

relate perguntas