migrations/2020-10-07-234221_fix_fast_triggers/down.sql

   1 CREATE OR REPLACE FUNCTION refresh_community ()
   2     RETURNS TRIGGER
   3     LANGUAGE plpgsql
   4     AS $$
   5 BEGIN
   6     IF (TG_OP = 'DELETE') THEN
   7         DELETE FROM community_aggregates_fast
   8         WHERE id = OLD.id;
   9     ELSIF (TG_OP = 'UPDATE') THEN
  10         DELETE FROM community_aggregates_fast
  11         WHERE id = OLD.id;
  12         INSERT INTO community_aggregates_fast
  13         SELECT
  14             *
  15         FROM
  16             community_aggregates_view
  17         WHERE
  18             id = NEW.id;
  19         -- Update user view due to owner changes
  20         DELETE FROM user_fast
  21         WHERE id = NEW.creator_id;
  22         INSERT INTO user_fast
  23         SELECT
  24             *
  25         FROM
  26             user_view
  27         WHERE
  28             id = NEW.creator_id;
  29         -- Update post view due to community changes
  30         DELETE FROM post_aggregates_fast
  31         WHERE community_id = NEW.id;
  32         INSERT INTO post_aggregates_fast
  33         SELECT
  34             *
  35         FROM
  36             post_aggregates_view
  37         WHERE
  38             community_id = NEW.id;
  39         -- TODO make sure this shows up in the users page ?
  40     ELSIF (TG_OP = 'INSERT') THEN
  41         INSERT INTO community_aggregates_fast
  42         SELECT
  43             *
  44         FROM
  45             community_aggregates_view
  46         WHERE
  47             id = NEW.id;
  48     END IF;
  49     RETURN NULL;
  50 END
  51 $$;
  52
  53 CREATE OR REPLACE FUNCTION refresh_user ()
  54     RETURNS TRIGGER
  55     LANGUAGE plpgsql
  56     AS $$
  57 BEGIN
  58     IF (TG_OP = 'DELETE') THEN
  59         DELETE FROM user_fast
  60         WHERE id = OLD.id;
  61     ELSIF (TG_OP = 'UPDATE') THEN
  62         DELETE FROM user_fast
  63         WHERE id = OLD.id;
  64         INSERT INTO user_fast
  65         SELECT
  66             *
  67         FROM
  68             user_view
  69         WHERE
  70             id = NEW.id;
  71         -- Refresh post_fast, cause of user info changes
  72         DELETE FROM post_aggregates_fast
  73         WHERE creator_id = NEW.id;
  74         INSERT INTO post_aggregates_fast
  75         SELECT
  76             *
  77         FROM
  78             post_aggregates_view
  79         WHERE
  80             creator_id = NEW.id;
  81         DELETE FROM comment_aggregates_fast
  82         WHERE creator_id = NEW.id;
  83         INSERT INTO comment_aggregates_fast
  84         SELECT
  85             *
  86         FROM
  87             comment_aggregates_view
  88         WHERE
  89             creator_id = NEW.id;
  90     ELSIF (TG_OP = 'INSERT') THEN
  91         INSERT INTO user_fast
  92         SELECT
  93             *
  94         FROM
  95             user_view
  96         WHERE
  97             id = NEW.id;
  98     END IF;
  99     RETURN NULL;
 100 END
 101 $$;
 102
 103 CREATE OR REPLACE FUNCTION refresh_post ()
 104     RETURNS TRIGGER
 105     LANGUAGE plpgsql
 106     AS $$
 107 BEGIN
 108     IF (TG_OP = 'DELETE') THEN
 109         DELETE FROM post_aggregates_fast
 110         WHERE id = OLD.id;
 111         -- Update community number of posts
 112         UPDATE
 113             community_aggregates_fast
 114         SET
 115             number_of_posts = number_of_posts - 1
 116         WHERE
 117             id = OLD.community_id;
 118     ELSIF (TG_OP = 'UPDATE') THEN
 119         DELETE FROM post_aggregates_fast
 120         WHERE id = OLD.id;
 121         INSERT INTO post_aggregates_fast
 122         SELECT
 123             *
 124         FROM
 125             post_aggregates_view
 126         WHERE
 127             id = NEW.id;
 128     ELSIF (TG_OP = 'INSERT') THEN
 129         INSERT INTO post_aggregates_fast
 130         SELECT
 131             *
 132         FROM
 133             post_aggregates_view
 134         WHERE
 135             id = NEW.id;
 136         -- Update that users number of posts, post score
 137         DELETE FROM user_fast
 138         WHERE id = NEW.creator_id;
 139         INSERT INTO user_fast
 140         SELECT
 141             *
 142         FROM
 143             user_view
 144         WHERE
 145             id = NEW.creator_id;
 146         -- Update community number of posts
 147         UPDATE
 148             community_aggregates_fast
 149         SET
 150             number_of_posts = number_of_posts + 1
 151         WHERE
 152             id = NEW.community_id;
 153         -- Update the hot rank on the post table
 154         -- TODO this might not correctly update it, using a 1 week interval
 155         UPDATE
 156             post_aggregates_fast AS paf
 157         SET
 158             hot_rank = pav.hot_rank
 159         FROM
 160             post_aggregates_view AS pav
 161         WHERE
 162             paf.id = pav.id
 163             AND (pav.published > ('now'::timestamp - '1 week'::interval));
 164     END IF;
 165     RETURN NULL;
 166 END
 167 $$;
 168
 169 CREATE OR REPLACE FUNCTION refresh_comment ()
 170     RETURNS TRIGGER
 171     LANGUAGE plpgsql
 172     AS $$
 173 BEGIN
 174     IF (TG_OP = 'DELETE') THEN
 175         DELETE FROM comment_aggregates_fast
 176         WHERE id = OLD.id;
 177         -- Update community number of comments
 178         UPDATE
 179             community_aggregates_fast AS caf
 180         SET
 181             number_of_comments = number_of_comments - 1
 182         FROM
 183             post AS p
 184         WHERE
 185             caf.id = p.community_id
 186             AND p.id = OLD.post_id;
 187     ELSIF (TG_OP = 'UPDATE') THEN
 188         DELETE FROM comment_aggregates_fast
 189         WHERE id = OLD.id;
 190         INSERT INTO comment_aggregates_fast
 191         SELECT
 192             *
 193         FROM
 194             comment_aggregates_view
 195         WHERE
 196             id = NEW.id;
 197     ELSIF (TG_OP = 'INSERT') THEN
 198         INSERT INTO comment_aggregates_fast
 199         SELECT
 200             *
 201         FROM
 202             comment_aggregates_view
 203         WHERE
 204             id = NEW.id;
 205         -- Update user view due to comment count
 206         UPDATE
 207             user_fast
 208         SET
 209             number_of_comments = number_of_comments + 1
 210         WHERE
 211             id = NEW.creator_id;
 212         -- Update post view due to comment count, new comment activity time, but only on new posts
 213         -- TODO this could be done more efficiently
 214         DELETE FROM post_aggregates_fast
 215         WHERE id = NEW.post_id;
 216         INSERT INTO post_aggregates_fast
 217         SELECT
 218             *
 219         FROM
 220             post_aggregates_view
 221         WHERE
 222             id = NEW.post_id;
 223         -- Force the hot rank as zero on week-older posts
 224         UPDATE
 225             post_aggregates_fast AS paf
 226         SET
 227             hot_rank = 0
 228         WHERE
 229             paf.id = NEW.post_id
 230             AND (paf.published < ('now'::timestamp - '1 week'::interval));
 231         -- Update community number of comments
 232         UPDATE
 233             community_aggregates_fast AS caf
 234         SET
 235             number_of_comments = number_of_comments + 1
 236         FROM
 237             post AS p
 238         WHERE
 239             caf.id = p.community_id
 240             AND p.id = NEW.post_id;
 241     END IF;
 242     RETURN NULL;
 243 END
 244 $$;
 245