I have a question about drafting a history table in Postgres.
The setup is that I have a table that contains a needs list. A site recalculates requested articles every five minutes and forwards this list to Postgres. The current "hot" list can then be retrieved from various client apps. So, every five minutes, the rows were linked to a specific location and deleted, then refilled with all the items that are now up to date. Imagine a screen on a wall in a warehouse where people are looking for urgent tasks. This is more or less a queue / notification table, not a real memory table.
What we are tracking in the list of requested articles are certain parts with IDs. It is valuable for us to collect data (or at least statistics) over time. Certain items may appear in the list each day, while others are rarely displayed. This can help guide buying decisions and so on.
This is the background, I'm in Postgres 11.5, so no generated columns. Does the strategy described below seem to be correct or can it be improved? The base table is called need and the history table is called need_history
need
– Saves the data of interest
— Has a NOW() Assigned to created_dts on INSERT as part of the table construction.
— Has a PER STATEMENT After triggers, the & # 39; transition table & # 39; to get the deleted lines.
– The instruction trigger INSERTS INTO need_history to preserve the data.
need_history
– It's almost a clone of necessity, but bolted with a few extra boxes. specifically, deleted_dts, assigned with NOW() by default when inserting the data and duration_seconds This saves the number of seconds that the record was in the demand table.
– Since this is PG 11.5, no columns generated, I need one EACH ROW trigger to calculate duration_seconds,
Less:need
with a delete trigger at statement level, which presses need_history,
need_history
with a row-level trigger for calculation duration_seconds because I did not create any columns in PG 11.x.
And to address the obvious question, no, not me to have to save the derived duration_seconds Value as it can be generated on-the-fly, but in this case I want to denormalize to simplify a variety of queries, sorts, and summaries.
My brain also says, "Ask Fillfactors", and I do not know why.
Below is the code for the initial setup if the summary above is not clear. I have not pushed through any data, so there can be mistakes.
I would be grateful for any advice or advice on how best to do this in Postgres.
BEGIN;
DROP TABLE IF EXISTS data.need CASCADE;
CREATE TABLE IF NOT EXISTS data.need (
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,
total_qty integer NOT NULL DEFAULT 0,
available_qty integer NOT NULL DEFAULT 0,
sterile_qty integer NOT NULL DEFAULT 0,
still_need_qty integer NOT NULL DEFAULT 0,
perc_down double precision NOT NULL DEFAULT '0',
usage_ integer NOT NULL DEFAULT 0,
need_for_case citext NOT NULL DEFAULT NULL,
status citext NOT NULL DEFAULT NULL,
created_dts timestamptz NOT NULL DEFAULT NOW(),
CONSTRAINT need_id_pkey
PRIMARY KEY (id)
);
ALTER TABLE data.need OWNER TO user_change_structure;
COMMIT;
/* Define the trigger function to copy the deleted rows to the history table. */
CREATE FUNCTION data.need_delete_copy_to_history()
RETURNS trigger AS
$BODY$
BEGIN
/* need.deleted_dts is auto-assigned on INSERT over in need, and
need.duration_seconds is calculated in an INSERT trigger (PG 11.5, not PG 12, no generated columns). */
INSERT INTO data.need_history
(id,
item_id,
facility_id,
hsys_id,
total_qty,
available_qty,
sterile_qty,
still_need_qty,
perc_down,
usage_,
need_for_case,
status,
created_dts)
SELECT id,
item_id,
facility_id,
hsys_id,
total_qty,
available_qty,
sterile_qty,
still_need_qty,
perc_down,
usage_,
need_for_case,
status,
created_dts
FROM deleted_rows;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$BODY$
LANGUAGE plpgsql;
/* Bind a trigger event to the function. */
DROP TRIGGER IF EXISTS trigger_need_after_delete ON data.need;
CREATE TRIGGER trigger_need_after_delete
AFTER DELETE ON data.need
REFERENCING OLD TABLE AS deleted_rows
FOR EACH STATEMENT EXECUTE FUNCTION data.need_delete_copy_to_history();
/* Define the table. */
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,
total_qty integer NOT NULL DEFAULT 0,
available_qty integer NOT NULL DEFAULT 0,
sterile_qty integer NOT NULL DEFAULT 0,
still_need_qty integer NOT NULL DEFAULT 0,
perc_down double precision NOT NULL DEFAULT '0',
usage_ integer NOT NULL DEFAULT 0,
need_for_case citext NOT NULL DEFAULT NULL,
status citext NOT NULL DEFAULT NULL,
created_dts timestamptz NOT NULL DEFAULT NULL,
deleted_dts timestamptz NOT NULL DEFAULT NOW(),
duration_seconds int4 NOT NULL DEFAULT 0,
CONSTRAINT need_history_id_pkey
PRIMARY KEY (id)
);
ALTER TABLE data.need_history OWNER TO user_change_structure;
COMMIT;
/* Define the trigger function to update the duration count.
In PG 12 we'll 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. */
DROP TRIGGER IF EXISTS trigger_need_history_before_insert ON data.need_history;
CREATE TRIGGER trigger_need_history_before_insert
BEFORE INSERT ON data.need_history
FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();```
