3 SELECT evergreen.upgrade_deps_block_check('1043', :eg_version);
5 ALTER TABLE action_trigger.event_definition
6 ADD COLUMN retention_interval INTERVAL;
8 CREATE OR REPLACE FUNCTION action_trigger.check_valid_retention_interval()
13 * 1. Retention intervals are alwyas allowed on active hooks.
14 * 2. On passive hooks, retention intervals are only allowed
15 * when the event definition has a max_delay value and the
16 * retention_interval value is greater than the difference
17 * beteween the delay and max_delay values.
19 PERFORM TRUE FROM action_trigger.hook
20 WHERE key = NEW.hook AND NOT passive;
26 IF NEW.max_delay IS NOT NULL THEN
27 IF EXTRACT(EPOCH FROM NEW.retention_interval) >
28 ABS(EXTRACT(EPOCH FROM (NEW.max_delay - NEW.delay))) THEN
29 RETURN NEW; -- all good
31 RAISE EXCEPTION 'retention_interval is too short';
34 RAISE EXCEPTION 'retention_interval requires max_delay';
39 CREATE TRIGGER is_valid_retention_interval
40 BEFORE INSERT OR UPDATE ON action_trigger.event_definition
41 FOR EACH ROW WHEN (NEW.retention_interval IS NOT NULL)
42 EXECUTE PROCEDURE action_trigger.check_valid_retention_interval();
44 CREATE OR REPLACE FUNCTION action_trigger.purge_events() RETURNS VOID AS $_$
46 * Deleting expired events without simultaneously deleting their outputs
47 * creates orphaned outputs. Deleting their outputs and all of the events
48 * linking back to them, plus any outputs those events link to is messy and
49 * inefficient. It's simpler to handle them in 2 sweeping steps.
51 * 1. Delete expired events.
52 * 2. Delete orphaned event outputs.
54 * This has the added benefit of removing outputs that may have been
55 * orphaned by some other process. Such outputs are not usuable by
58 * This does not guarantee that all events within an event group are
59 * purged at the same time. In such cases, the remaining events will
60 * be purged with the next instance of the purge (or soon thereafter).
61 * This is another nod toward efficiency over completeness of old
62 * data that's circling the bit bucket anyway.
66 DELETE FROM action_trigger.event WHERE id IN (
68 FROM action_trigger.event evt
69 JOIN action_trigger.event_definition def ON (def.id = evt.event_def)
70 WHERE def.retention_interval IS NOT NULL
71 AND evt.state <> 'pending'
72 AND evt.update_time < (NOW() - def.retention_interval)
75 WITH linked_outputs AS (
76 SELECT templates.id AS id FROM (
77 SELECT DISTINCT(template_output) AS id
78 FROM action_trigger.event WHERE template_output IS NOT NULL
80 SELECT DISTINCT(error_output) AS id
81 FROM action_trigger.event WHERE error_output IS NOT NULL
83 SELECT DISTINCT(async_output) AS id
84 FROM action_trigger.event WHERE async_output IS NOT NULL
86 ) DELETE FROM action_trigger.event_output
87 WHERE id NOT IN (SELECT id FROM linked_outputs);
96 DROP FUNCTION IF EXISTS action_trigger.purge_events();
97 DROP TRIGGER IF EXISTS is_valid_retention_interval ON action_trigger.event_definition;
98 DROP FUNCTION IF EXISTS action_trigger.check_valid_retention_interval();
99 ALTER TABLE action_trigger.event_definition DROP COLUMN retention_interval;