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

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