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

   1 -- Forgot to add hot rank active to these two triggers
   2 CREATE OR REPLACE FUNCTION refresh_post ()
   3     RETURNS TRIGGER
   4     LANGUAGE plpgsql
   5     AS $$
   6 BEGIN
   7     IF (TG_OP = 'DELETE') THEN
   8         DELETE FROM post_aggregates_fast
   9         WHERE id = OLD.id;
  10         -- Update community number of posts
  11         UPDATE
  12             community_aggregates_fast
  13         SET
  14             number_of_posts = number_of_posts - 1
  15         WHERE
  16             id = OLD.community_id;
  17     ELSIF (TG_OP = 'UPDATE') THEN
  18         DELETE FROM post_aggregates_fast
  19         WHERE id = OLD.id;
  20         INSERT INTO post_aggregates_fast
  21         SELECT
  22             *
  23         FROM
  24             post_aggregates_view
  25         WHERE
  26             id = NEW.id
  27         ON CONFLICT (id)
  28             DO NOTHING;
  29     ELSIF (TG_OP = 'INSERT') THEN
  30         INSERT INTO post_aggregates_fast
  31         SELECT
  32             *
  33         FROM
  34             post_aggregates_view
  35         WHERE
  36             id = NEW.id;
  37         -- Update that users number of posts, post score
  38         DELETE FROM user_fast
  39         WHERE id = NEW.creator_id;
  40         INSERT INTO user_fast
  41         SELECT
  42             *
  43         FROM
  44             user_view
  45         WHERE
  46             id = NEW.creator_id
  47         ON CONFLICT (id)
  48             DO NOTHING;
  49         -- Update community number of posts
  50         UPDATE
  51             community_aggregates_fast
  52         SET
  53             number_of_posts = number_of_posts + 1
  54         WHERE
  55             id = NEW.community_id;
  56         -- Update the hot rank on the post table
  57         -- TODO this might not correctly update it, using a 1 week interval
  58         UPDATE
  59             post_aggregates_fast AS paf
  60         SET
  61             hot_rank = pav.hot_rank,
  62             hot_rank_active = pav.hot_rank_active
  63         FROM
  64             post_aggregates_view AS pav
  65         WHERE
  66             paf.id = pav.id
  67             AND (pav.published > ('now'::timestamp - '1 week'::interval));
  68     END IF;
  69     RETURN NULL;
  70 END
  71 $$;
  72
  73 CREATE OR REPLACE FUNCTION refresh_comment ()
  74     RETURNS TRIGGER
  75     LANGUAGE plpgsql
  76     AS $$
  77 BEGIN
  78     IF (TG_OP = 'DELETE') THEN
  79         DELETE FROM comment_aggregates_fast
  80         WHERE id = OLD.id;
  81         -- Update community number of comments
  82         UPDATE
  83             community_aggregates_fast AS caf
  84         SET
  85             number_of_comments = number_of_comments - 1
  86         FROM
  87             post AS p
  88         WHERE
  89             caf.id = p.community_id
  90             AND p.id = OLD.post_id;
  91     ELSIF (TG_OP = 'UPDATE') THEN
  92         DELETE FROM comment_aggregates_fast
  93         WHERE id = OLD.id;
  94         INSERT INTO comment_aggregates_fast
  95         SELECT
  96             *
  97         FROM
  98             comment_aggregates_view
  99         WHERE
 100             id = NEW.id
 101         ON CONFLICT (id)
 102             DO NOTHING;
 103     ELSIF (TG_OP = 'INSERT') THEN
 104         INSERT INTO comment_aggregates_fast
 105         SELECT
 106             *
 107         FROM
 108             comment_aggregates_view
 109         WHERE
 110             id = NEW.id;
 111         -- Update user view due to comment count
 112         UPDATE
 113             user_fast
 114         SET
 115             number_of_comments = number_of_comments + 1
 116         WHERE
 117             id = NEW.creator_id;
 118         -- Update post view due to comment count, new comment activity time, but only on new posts
 119         -- TODO this could be done more efficiently
 120         DELETE FROM post_aggregates_fast
 121         WHERE id = NEW.post_id;
 122         INSERT INTO post_aggregates_fast
 123         SELECT
 124             *
 125         FROM
 126             post_aggregates_view
 127         WHERE
 128             id = NEW.post_id
 129         ON CONFLICT (id)
 130             DO NOTHING;
 131         -- Update the comment hot_ranks as of last week
 132         UPDATE
 133             comment_aggregates_fast AS caf
 134         SET
 135             hot_rank = cav.hot_rank,
 136             hot_rank_active = cav.hot_rank_active
 137         FROM
 138             comment_aggregates_view AS cav
 139         WHERE
 140             caf.id = cav.id
 141             AND (cav.published > ('now'::timestamp - '1 week'::interval));
 142         -- Update the post ranks
 143         UPDATE
 144             post_aggregates_fast AS paf
 145         SET
 146             hot_rank = pav.hot_rank,
 147             hot_rank_active = pav.hot_rank_active
 148         FROM
 149             post_aggregates_view AS pav
 150         WHERE
 151             paf.id = pav.id
 152             AND (pav.published > ('now'::timestamp - '1 week'::interval));
 153         -- Force the hot rank active as zero on 2 day-older posts (necro-bump)
 154         UPDATE
 155             post_aggregates_fast AS paf
 156         SET
 157             hot_rank_active = 0
 158         WHERE
 159             paf.id = NEW.post_id
 160             AND (paf.published < ('now'::timestamp - '2 days'::interval));
 161         -- Update community number of comments
 162         UPDATE
 163             community_aggregates_fast AS caf
 164         SET
 165             number_of_comments = number_of_comments + 1
 166         FROM
 167             post AS p
 168         WHERE
 169             caf.id = p.community_id
 170             AND p.id = NEW.post_id;
 171     END IF;
 172     RETURN NULL;
 173 END
 174 $$;
 175