migrations/2020-10-10-035723_fix_fast_triggers_2/up.sql

   1 -- Forgot to add hot rank active to these two triggers
   2
   3 create or replace function refresh_post()
   4 returns trigger language plpgsql
   5 as $$
   6 begin
   7   IF (TG_OP = 'DELETE') THEN
   8     delete from post_aggregates_fast where id = OLD.id;
   9
  10     -- Update community number of posts
  11     update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
  12   ELSIF (TG_OP = 'UPDATE') THEN
  13     delete from post_aggregates_fast where id = OLD.id;
  14     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id on conflict (id) do nothing;
  15   ELSIF (TG_OP = 'INSERT') THEN
  16     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
  17
  18     -- Update that users number of posts, post score
  19     delete from user_fast where id = NEW.creator_id;
  20     insert into user_fast select * from user_view where id = NEW.creator_id on conflict (id) do nothing;
  21
  22     -- Update community number of posts
  23     update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
  24
  25     -- Update the hot rank on the post table
  26     -- TODO this might not correctly update it, using a 1 week interval
  27     update post_aggregates_fast as paf
  28     set
  29       hot_rank = pav.hot_rank,
  30       hot_rank_active = pav.hot_rank_active
  31     from post_aggregates_view as pav
  32     where paf.id = pav.id  and (pav.published > ('now'::timestamp - '1 week'::interval));
  33   END IF;
  34
  35   return null;
  36 end $$;
  37
  38 create or replace function refresh_comment()
  39 returns trigger language plpgsql
  40 as $$
  41 begin
  42   IF (TG_OP = 'DELETE') THEN
  43     delete from comment_aggregates_fast where id = OLD.id;
  44
  45     -- Update community number of comments
  46     update community_aggregates_fast as caf
  47     set number_of_comments = number_of_comments - 1
  48     from post as p
  49     where caf.id = p.community_id and p.id = OLD.post_id;
  50
  51   ELSIF (TG_OP = 'UPDATE') THEN
  52     delete from comment_aggregates_fast where id = OLD.id;
  53     insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id on conflict (id) do nothing;
  54   ELSIF (TG_OP = 'INSERT') THEN
  55     insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
  56
  57     -- Update user view due to comment count
  58     update user_fast
  59     set number_of_comments = number_of_comments + 1
  60     where id = NEW.creator_id;
  61
  62     -- Update post view due to comment count, new comment activity time, but only on new posts
  63     -- TODO this could be done more efficiently
  64     delete from post_aggregates_fast where id = NEW.post_id;
  65     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id on conflict (id) do nothing;
  66
  67     -- Update the comment hot_ranks as of last week
  68     update comment_aggregates_fast as caf
  69     set
  70       hot_rank = cav.hot_rank,
  71       hot_rank_active = cav.hot_rank_active
  72     from comment_aggregates_view as cav
  73     where caf.id = cav.id and (cav.published > ('now'::timestamp - '1 week'::interval));
  74
  75     -- Update the post ranks
  76     update post_aggregates_fast as paf
  77     set
  78       hot_rank = pav.hot_rank,
  79       hot_rank_active = pav.hot_rank_active
  80     from post_aggregates_view as pav
  81     where paf.id = pav.id  and (pav.published > ('now'::timestamp - '1 week'::interval));
  82
  83     -- Force the hot rank active as zero on 2 day-older posts (necro-bump)
  84     update post_aggregates_fast as paf
  85     set hot_rank_active = 0
  86     where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '2 days'::interval));
  87
  88     -- Update community number of comments
  89     update community_aggregates_fast as caf
  90     set number_of_comments = number_of_comments + 1
  91     from post as p
  92     where caf.id = p.community_id and p.id = NEW.post_id;
  93
  94   END IF;
  95
  96   return null;
  97 end $$;