sql/big_at_deletion.sql

   1 \timing on
   2 BEGIN;
   3
   4 -- shove the ones we want to keep into a temp table
   5
   6 CREATE TEMPORARY TABLE tmp_at_events ON COMMIT DROP AS
   7 SELECT * FROM action_trigger.event e
   8         WHERE EXISTS (
   9                 SELECT 1
  10                 FROM action_trigger.event_definition d
  11                 WHERE e.event_def = d.id
  12                 AND d.retention_interval is null)
  13         OR EXISTS (
  14                 SELECT 1
  15                 FROM action_trigger.event_definition d
  16                 WHERE e.event_def = d.id AND e.update_time > now() - d.retention_interval
  17         OR e.state = 'pending'
  18 );
  19
  20
  21 -- then blow everything away
  22 TRUNCATE TABLE action_trigger.event;
  23
  24 -- then put back the ones we want
  25 INSERT INTO action_trigger.event
  26 SELECT * FROM tmp_at_events;
  27
  28 COMMIT; -- required since we alter the table in the next part
  29
  30
  31 BEGIN;
  32
  33 -- drop constraints to speed things up
  34 ALTER TABLE action_trigger.event DROP CONSTRAINT event_template_output_fkey;
  35 ALTER TABLE action_trigger.event DROP CONSTRAINT event_async_output_fkey;
  36 ALTER TABLE action_trigger.event DROP CONSTRAINT event_error_output_fkey;
  37
  38 -- this comes directly from the action_trigger.purge_events function
  39 WITH linked_outputs AS (
  40     SELECT templates.id AS id FROM (
  41         SELECT DISTINCT(template_output) AS id
  42             FROM action_trigger.event WHERE template_output IS NOT NULL
  43         UNION
  44         SELECT DISTINCT(error_output) AS id
  45             FROM action_trigger.event WHERE error_output IS NOT NULL
  46         UNION
  47         SELECT DISTINCT(async_output) AS id
  48             FROM action_trigger.event WHERE async_output IS NOT NULL
  49     ) templates
  50 ) DELETE FROM action_trigger.event_output
  51     WHERE id NOT IN (SELECT id FROM linked_outputs);
  52
  53 -- restore constraints
  54 ALTER TABLE action_trigger.event ADD CONSTRAINT event_template_output_fkey FOREIGN KEY (template_output) REFERENCES action_trigger.event_output(id);
  55 ALTER TABLE action_trigger.event ADD CONSTRAINT event_async_output_fkey FOREIGN KEY (async_output) REFERENCES action_trigger.event_output(id);
  56 ALTER TABLE action_trigger.event ADD CONSTRAINT event_error_output_fkey FOREIGN KEY (error_output) REFERENCES action_trigger.event_output(id);
  57
  58 COMMIT;
  59
  60 ANALYZE action_trigger.event;
  61 VACUUM FULL ANALYZE action_trigger.event_output;