1

I asked a question about History table design for deletions in PG 11.5, and received a suggestion to partition the table. This is an excellent idea as the table may become huge, and the information content is low. Meaning, I'll end up wanting to purge data.

When I went to reimplement the table with partition, I discovered the PG (11 and 12) do not support BEFORE ROW triggers on the master partition table, only on the individual partition. Which leads to a ton of binding code. Is there a better way? All I've got the trigger for in this case is to subtract two timestamps and store the seconds. 11.5, so no generated columns.

I'm included the code even though it's quite long, as that's kind of the point.

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

1 Answer 1

4

I don't know of any built-in solution to this; I think you ultimately need to run your own CREATE TRIGGER statement for each new partition.

There are a couple of ways to automate this. This function will come in handy:

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;

If you:

  • Really need the CREATE TABLE statement to add the trigger automatically, and
  • Have access to a superuser role

...then you can write an event trigger to fire on partition creation and install the trigger automatically:

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

However, in most cases, it's probably simpler to wrap the whole partition creation process in a function:

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;

This may be worth doing regardless of the trigger requirement: it keeps the details of your partitioning scheme nicely encapsulated, ensures that partition ranges are constructed correctly and naming conventions are followed, as well as making the whole process of adding partitions a lot more user-friendly. For example, the partitions in your script can be created with a single command:

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

All that said, if your history tables are INSERT-only, and the only source of new records is the statement trigger on data.need, then I probably wouldn't go to all this trouble; I'd just calculate the duration_seconds value in the INSERT statement itself.

To be paranoid, you could also add a CHECK constraint on data.need_history (which will be inherited by all partitions) to verify that the field is being set correctly:

CHECK(
  duration_seconds * interval '1 second' = 
    DATE_TRUNC('second', deleted_dts) - DATE_TRUNC('second', created_dts)
 )
5
  • By the way, as noted in the docs: When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound. - in other words, your upper bounds should probably be specified as the first day of the following month. I made this assumption in create_new_partition(). Nov 24, 2019 at 1:59
  • Wow, that's an incredibly detailed and comprehensive answer, thanks! Yes, you're right, simply making the calculation in the INSERT would be sufficient. In this case, I'm kind of taking the opportunity to get my hands dirty with partitioning. I had not noticed about the ranges as you noted in your comment. So, it should be FROM ('2020-01-01') TO ('2020-02-01') and FROM ('2020-02-01') TO ('2019-03-01'). I think that's confusing, but it is what it is. I'm very glad that you pointed it out. I've defensively set up a DEFAULT which should never have records, and a script to check. Nov 24, 2019 at 5:38
  • Just looked at the docs. Nope, I would not have figured that out from the text or examples. I'll go fix my definitions now. Nov 24, 2019 at 5:41
  • I rewrote the code, updated the example and have to say that the PG behavior makes things simpler. Instead of having to figure out the end day for each month, you only have to list the start date of the next month, which is simpler to get right. But, glad to know about this details! Next up, I have to look at the details of adding indexes for my grouping queries. And, yes, I generate most of my DDL code with scripts, that's a good way to avoid errors. I haven't done so in this case, and should. Nov 24, 2019 at 5:53
  • Yeah, the "inclusive lower, exclusive upper" pattern seems a little unintuitive at first for discrete values like integers or dates. But in the context of "continuous" types like timestamps or floats, the reason becomes clear pretty quickly. Consider this: your partition key isn't a date, it's a timestamp, and so the correct inclusive upper bound isn't the last day of the month, it's the last microsecond of the month, e.g. '2020-01-31 23:59:59.999999'. (And this is for a type with a well-defined decimal precision; the situation with floating-point values would be quite a lot worse.) Nov 24, 2019 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.