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

   1 create or replace function person_aggregates_comment_count()
   2     returns trigger language plpgsql
   3 as $$
   4 begin
   5     IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
   6         update person_aggregates
   7         set comment_count = comment_count + 1 where person_id = NEW.creator_id;
   8     ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
   9         update person_aggregates
  10         set comment_count = comment_count - 1 where person_id = OLD.creator_id;
  11
  12         -- If the comment gets deleted, the score calculation trigger won't fire,
  13         -- so you need to re-calculate
  14         update person_aggregates ua
  15         set comment_score = cd.score
  16         from (
  17                  select u.id,
  18                         coalesce(0, sum(cl.score)) as score
  19                         -- User join because comments could be empty
  20                  from person u
  21                           left join comment c on u.id = c.creator_id and c.deleted = 'f' and c.removed = 'f'
  22                           left join comment_like cl on c.id = cl.comment_id
  23                  group by u.id
  24              ) cd
  25         where ua.person_id = OLD.creator_id;
  26     END IF;
  27     return null;
  28 end $$;
  29
  30 create or replace function person_aggregates_post_count()
  31     returns trigger language plpgsql
  32 as $$
  33 begin
  34     IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
  35         update person_aggregates
  36         set post_count = post_count + 1 where person_id = NEW.creator_id;
  37
  38     ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
  39         update person_aggregates
  40         set post_count = post_count - 1 where person_id = OLD.creator_id;
  41
  42         -- If the post gets deleted, the score calculation trigger won't fire,
  43         -- so you need to re-calculate
  44         update person_aggregates ua
  45         set post_score = pd.score
  46         from (
  47                  select u.id,
  48                         coalesce(0, sum(pl.score)) as score
  49                         -- User join because posts could be empty
  50                  from person u
  51                           left join post p on u.id = p.creator_id and p.deleted = 'f' and p.removed = 'f'
  52                           left join post_like pl on p.id = pl.post_id
  53                  group by u.id
  54              ) pd
  55         where ua.person_id = OLD.creator_id;
  56
  57     END IF;
  58     return null;
  59 end $$;
  60
  61 create or replace function community_aggregates_comment_count()
  62     returns trigger language plpgsql
  63 as $$
  64 begin
  65   IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
  66 update community_aggregates ca
  67 set comments = comments + 1 from comment c, post p
  68 where p.id = c.post_id
  69   and p.id = NEW.post_id
  70   and ca.community_id = p.community_id;
  71 ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
  72 update community_aggregates ca
  73 set comments = comments - 1 from comment c, post p
  74 where p.id = c.post_id
  75   and p.id = OLD.post_id
  76   and ca.community_id = p.community_id;
  77
  78 END IF;
  79 return null;
  80 end $$;