migrations/2023-07-27-134652_remove-expensive-broken-trigger/down.sql

   1 CREATE OR REPLACE FUNCTION person_aggregates_comment_count ()
   2     RETURNS TRIGGER
   3     LANGUAGE plpgsql
   4     AS $$
   5 BEGIN
   6     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
   7         UPDATE
   8             person_aggregates
   9         SET
  10             comment_count = comment_count + 1
  11         WHERE
  12             person_id = NEW.creator_id;
  13     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
  14         UPDATE
  15             person_aggregates
  16         SET
  17             comment_count = comment_count - 1
  18         WHERE
  19             person_id = OLD.creator_id;
  20         -- If the comment gets deleted, the score calculation trigger won't fire,
  21         -- so you need to re-calculate
  22         UPDATE
  23             person_aggregates ua
  24         SET
  25             comment_score = cd.score
  26         FROM (
  27             SELECT
  28                 u.id,
  29                 coalesce(0, sum(cl.score)) AS score
  30                 -- User join because comments could be empty
  31             FROM
  32                 person u
  33             LEFT JOIN comment c ON u.id = c.creator_id
  34                 AND c.deleted = 'f'
  35                 AND c.removed = 'f'
  36         LEFT JOIN comment_like cl ON c.id = cl.comment_id
  37     GROUP BY
  38         u.id) cd
  39     WHERE
  40         ua.person_id = OLD.creator_id;
  41     END IF;
  42     RETURN NULL;
  43 END
  44 $$;
  45
  46 CREATE OR REPLACE FUNCTION person_aggregates_post_count ()
  47     RETURNS TRIGGER
  48     LANGUAGE plpgsql
  49     AS $$
  50 BEGIN
  51     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
  52         UPDATE
  53             person_aggregates
  54         SET
  55             post_count = post_count + 1
  56         WHERE
  57             person_id = NEW.creator_id;
  58     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
  59         UPDATE
  60             person_aggregates
  61         SET
  62             post_count = post_count - 1
  63         WHERE
  64             person_id = OLD.creator_id;
  65         -- If the post gets deleted, the score calculation trigger won't fire,
  66         -- so you need to re-calculate
  67         UPDATE
  68             person_aggregates ua
  69         SET
  70             post_score = pd.score
  71         FROM (
  72             SELECT
  73                 u.id,
  74                 coalesce(0, sum(pl.score)) AS score
  75                 -- User join because posts could be empty
  76             FROM
  77                 person u
  78             LEFT JOIN post p ON u.id = p.creator_id
  79                 AND p.deleted = 'f'
  80                 AND p.removed = 'f'
  81         LEFT JOIN post_like pl ON p.id = pl.post_id
  82     GROUP BY
  83         u.id) pd
  84     WHERE
  85         ua.person_id = OLD.creator_id;
  86     END IF;
  87     RETURN NULL;
  88 END
  89 $$;
  90
  91 CREATE OR REPLACE FUNCTION community_aggregates_comment_count ()
  92     RETURNS TRIGGER
  93     LANGUAGE plpgsql
  94     AS $$
  95 BEGIN
  96     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
  97         UPDATE
  98             community_aggregates ca
  99         SET
 100             comments = comments + 1
 101         FROM
 102             comment c,
 103             post p
 104         WHERE
 105             p.id = c.post_id
 106             AND p.id = NEW.post_id
 107             AND ca.community_id = p.community_id;
 108     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
 109         UPDATE
 110             community_aggregates ca
 111         SET
 112             comments = comments - 1
 113         FROM
 114             comment c,
 115             post p
 116         WHERE
 117             p.id = c.post_id
 118             AND p.id = OLD.post_id
 119             AND ca.community_id = p.community_id;
 120     END IF;
 121     RETURN NULL;
 122 END
 123 $$;
 124