From ea17c7f5912ec7fdba08bcf54e29c67656f59c1f Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Fri, 30 Nov 2018 09:20:51 -0500 Subject: [PATCH] adding action/trigger deletion script --- sql/big_at_deletion.sql | 61 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 61 insertions(+) create mode 100644 sql/big_at_deletion.sql diff --git a/sql/big_at_deletion.sql b/sql/big_at_deletion.sql new file mode 100644 index 0000000..b664755 --- /dev/null +++ b/sql/big_at_deletion.sql @@ -0,0 +1,61 @@ +\timing on +BEGIN; + +-- shove the ones we want to keep into a temp table + +CREATE TEMPORARY TABLE tmp_at_events ON COMMIT DROP AS +SELECT * FROM action_trigger.event e + WHERE EXISTS ( + SELECT 1 + FROM action_trigger.event_definition d + WHERE e.event_def = d.id + AND d.retention_interval is null) + OR EXISTS ( + SELECT 1 + FROM action_trigger.event_definition d + WHERE e.event_def = d.id AND e.update_time > now() - d.retention_interval + OR e.state = 'pending' +); + + +-- then blow everything away +TRUNCATE TABLE action_trigger.event; + +-- then put back the ones we want +INSERT INTO action_trigger.event +SELECT * FROM tmp_at_events; + +COMMIT; -- required since we alter the table in the next part + + +BEGIN; + +-- drop constraints to speed things up +ALTER TABLE action_trigger.event DROP CONSTRAINT event_template_output_fkey; +ALTER TABLE action_trigger.event DROP CONSTRAINT event_async_output_fkey; +ALTER TABLE action_trigger.event DROP CONSTRAINT event_error_output_fkey; + +-- this comes directly from the action_trigger.purge_events function +WITH linked_outputs AS ( + SELECT templates.id AS id FROM ( + SELECT DISTINCT(template_output) AS id + FROM action_trigger.event WHERE template_output IS NOT NULL + UNION + SELECT DISTINCT(error_output) AS id + FROM action_trigger.event WHERE error_output IS NOT NULL + UNION + SELECT DISTINCT(async_output) AS id + FROM action_trigger.event WHERE async_output IS NOT NULL + ) templates +) DELETE FROM action_trigger.event_output + WHERE id NOT IN (SELECT id FROM linked_outputs); + +-- restore constraints +ALTER TABLE action_trigger.event ADD CONSTRAINT event_template_output_fkey FOREIGN KEY (template_output) REFERENCES action_trigger.event_output(id); +ALTER TABLE action_trigger.event ADD CONSTRAINT event_async_output_fkey FOREIGN KEY (async_output) REFERENCES action_trigger.event_output(id); +ALTER TABLE action_trigger.event ADD CONSTRAINT event_error_output_fkey FOREIGN KEY (error_output) REFERENCES action_trigger.event_output(id); + +COMMIT; + +ANALYZE action_trigger.event; +VACUUM FULL ANALYZE action_trigger.event_output; -- 2.11.0