Dyrcona

action_trigger-cleanup.sql

May 31st, 2018
394
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* ---------------------------------------------------------------
  2.  * Copyright (C) 2018 CW MARS, INC.
  3.  * Jason Stephenson <jstephenson@cwmars.org>
  4.  *
  5.  * This program is free software; you can redistribute it and/or modify
  6.  * it under the terms of the GNU General Public License as published by
  7.  * the Free Software Foundation; either version 2 of the License, or
  8.  * (at your option) any later version.
  9.  *
  10.  * This program is distributed in the hope that it will be useful,
  11.  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12.  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13.  * GNU General Public License for more details.
  14.  */ ---------------------------------------------------------------
  15. BEGIN;
  16.  
  17. -- First, lock the tables to prevent updates.
  18. LOCK TABLE action_trigger.event, action_trigger.event_output IN EXCLUSIVE MODE;
  19.  
  20. -- Second, drop the constraints that seem to block us on truncate.
  21. ALTER TABLE action_trigger.event DROP CONSTRAINT event_async_output_fkey;
  22. ALTER TABLE action_trigger.event DROP CONSTRAINT event_error_output_fkey;
  23. ALTER TABLE action_trigger.event DROP CONSTRAINT event_template_output_fkey;
  24.  
  25. -- Copy the events for 2018.
  26. CREATE TEMP TABLE temp_ate ON COMMIT DROP AS
  27. SELECT *
  28. FROM action_trigger.event
  29. WHERE add_time > '2017-12-31 23:59:59';
  30.  
  31. -- Copy the output of the above events.
  32. CREATE TEMP TABLE temp_ato ON COMMIT DROP AS
  33. SELECT DISTINCT event_output.*
  34. FROM action_trigger.event_output
  35. JOIN temp_ate
  36. ON event_output.id = temp_ate.template_output
  37. UNION DISTINCT
  38. SELECT DISTINCT event_output.*
  39. FROM action_trigger.event_output
  40. JOIN temp_ate
  41. ON event_output.id = temp_ate.error_output
  42. UNION DISTINCT
  43. SELECT DISTINCT event_output.*
  44. FROM action_trigger.event_output
  45. JOIN temp_ate
  46. ON event_output.id = temp_ate.async_output;
  47.  
  48. -- Truncate the tables.
  49. TRUNCATE TABLE action_trigger.event;
  50. TRUNCATE TABLE action_trigger.event_output;
  51.  
  52. -- Copy the output back to the event_output table.
  53. INSERT INTO action_trigger.event_output
  54. SELECT *
  55. FROM temp_ato;
  56.  
  57. -- Copy the events back the event table.
  58. INSERT INTO action_trigger.event
  59. SELECT *
  60. FROM temp_ate;
  61.  
  62. COMMIT;
  63.  
  64. -- Finally, re-add the constraints.  This is done outside the
  65. -- transaction because you can't modify a table with pending updates.
  66. ALTER TABLE action_trigger.event ADD CONSTRAINT event_async_output_fkey FOREIGN KEY (async_output)
  67.       REFERENCES action_trigger.event_output (id); --DEFERRABLE INITIALLY DEFERRED;
  68. ALTER TABLE action_trigger.event ADD CONSTRAINT event_error_output_fkey FOREIGN KEY (error_output)
  69.       REFERENCES action_trigger.event_output (id); --DEFERRABLE INITIALLY DEFERRED;
  70. ALTER TABLE action_trigger.event ADD CONSTRAINT event_template_output_fkey FOREIGN KEY (template_output)
  71.       REFERENCES action_trigger.event_output (id); --DEFERRABLE INITIALLY DEFERRED;
  72.  
  73. -- The DEFERRABLE INITIALLY DEFERRED are commented out above because I
  74. -- think that may be a useful change to make in the future, but I want
  75. -- to see if the standard delete function still works with the normal
  76. -- constraints.
RAW Paste Data