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

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