migrations/2023-07-26-000217_create_controversial_indexes/up.sql

   1 -- Need to add immutable to the controversy_rank function in order to index by it
   2 -- Controversy Rank:
   3 --      if downvotes <= 0 or upvotes <= 0:
   4 --          0
   5 --      else:
   6 --          (upvotes + downvotes) * min(upvotes, downvotes) / max(upvotes, downvotes)
   7 CREATE OR REPLACE FUNCTION controversy_rank (upvotes numeric, downvotes numeric)
   8     RETURNS float
   9     AS $$
  10 BEGIN
  11     IF downvotes <= 0 OR upvotes <= 0 THEN
  12         RETURN 0;
  13     ELSE
  14         RETURN (upvotes + downvotes) * CASE WHEN upvotes > downvotes THEN
  15             downvotes::float / upvotes::float
  16         ELSE
  17             upvotes::float / downvotes::float
  18         END;
  19     END IF;
  20 END;
  21 $$
  22 LANGUAGE plpgsql
  23 IMMUTABLE;
  24
  25 -- Aggregates
  26 ALTER TABLE post_aggregates
  27     ADD COLUMN controversy_rank float NOT NULL DEFAULT 0;
  28
  29 ALTER TABLE comment_aggregates
  30     ADD COLUMN controversy_rank float NOT NULL DEFAULT 0;
  31
  32 -- Populate them initially
  33 -- Note: After initial population, these are updated with vote triggers
  34 UPDATE
  35     post_aggregates
  36 SET
  37     controversy_rank = controversy_rank (upvotes::numeric, downvotes::numeric);
  38
  39 UPDATE
  40     comment_aggregates
  41 SET
  42     controversy_rank = controversy_rank (upvotes::numeric, downvotes::numeric);
  43
  44 -- Create single column indexes
  45 CREATE INDEX idx_post_aggregates_featured_local_controversy ON post_aggregates (featured_local DESC, controversy_rank DESC);
  46
  47 CREATE INDEX idx_post_aggregates_featured_community_controversy ON post_aggregates (featured_community DESC, controversy_rank DESC);
  48
  49 CREATE INDEX idx_comment_aggregates_controversy ON comment_aggregates (controversy_rank DESC);
  50
  51 -- Update post_aggregates_score trigger function to include controversy_rank update
  52 CREATE OR REPLACE FUNCTION post_aggregates_score ()
  53     RETURNS TRIGGER
  54     LANGUAGE plpgsql
  55     AS $$
  56 BEGIN
  57     IF (TG_OP = 'INSERT') THEN
  58         UPDATE
  59             post_aggregates pa
  60         SET
  61             score = score + NEW.score,
  62             upvotes = CASE WHEN NEW.score = 1 THEN
  63                 upvotes + 1
  64             ELSE
  65                 upvotes
  66             END,
  67             downvotes = CASE WHEN NEW.score = - 1 THEN
  68                 downvotes + 1
  69             ELSE
  70                 downvotes
  71             END,
  72             controversy_rank = controversy_rank (pa.upvotes + CASE WHEN NEW.score = 1 THEN
  73                     1
  74                 ELSE
  75                     0
  76                 END::numeric, pa.downvotes + CASE WHEN NEW.score = - 1 THEN
  77                     1
  78                 ELSE
  79                     0
  80                 END::numeric)
  81         WHERE
  82             pa.post_id = NEW.post_id;
  83     ELSIF (TG_OP = 'DELETE') THEN
  84         -- Join to post because that post may not exist anymore
  85         UPDATE
  86             post_aggregates pa
  87         SET
  88             score = score - OLD.score,
  89             upvotes = CASE WHEN OLD.score = 1 THEN
  90                 upvotes - 1
  91             ELSE
  92                 upvotes
  93             END,
  94             downvotes = CASE WHEN OLD.score = - 1 THEN
  95                 downvotes - 1
  96             ELSE
  97                 downvotes
  98             END,
  99             controversy_rank = controversy_rank (pa.upvotes + CASE WHEN NEW.score = 1 THEN
 100                     1
 101                 ELSE
 102                     0
 103                 END::numeric, pa.downvotes + CASE WHEN NEW.score = - 1 THEN
 104                     1
 105                 ELSE
 106                     0
 107                 END::numeric)
 108         FROM
 109             post p
 110         WHERE
 111             pa.post_id = p.id
 112             AND pa.post_id = OLD.post_id;
 113     END IF;
 114     RETURN NULL;
 115 END
 116 $$;
 117
 118 -- Update comment_aggregates_score trigger function to include controversy_rank update
 119 CREATE OR REPLACE FUNCTION comment_aggregates_score ()
 120     RETURNS TRIGGER
 121     LANGUAGE plpgsql
 122     AS $$
 123 BEGIN
 124     IF (TG_OP = 'INSERT') THEN
 125         UPDATE
 126             comment_aggregates ca
 127         SET
 128             score = score + NEW.score,
 129             upvotes = CASE WHEN NEW.score = 1 THEN
 130                 upvotes + 1
 131             ELSE
 132                 upvotes
 133             END,
 134             downvotes = CASE WHEN NEW.score = - 1 THEN
 135                 downvotes + 1
 136             ELSE
 137                 downvotes
 138             END,
 139             controversy_rank = controversy_rank (ca.upvotes + CASE WHEN NEW.score = 1 THEN
 140                     1
 141                 ELSE
 142                     0
 143                 END::numeric, ca.downvotes + CASE WHEN NEW.score = - 1 THEN
 144                     1
 145                 ELSE
 146                     0
 147                 END::numeric)
 148         WHERE
 149             ca.comment_id = NEW.comment_id;
 150     ELSIF (TG_OP = 'DELETE') THEN
 151         -- Join to comment because that comment may not exist anymore
 152         UPDATE
 153             comment_aggregates ca
 154         SET
 155             score = score - OLD.score,
 156             upvotes = CASE WHEN OLD.score = 1 THEN
 157                 upvotes - 1
 158             ELSE
 159                 upvotes
 160             END,
 161             downvotes = CASE WHEN OLD.score = - 1 THEN
 162                 downvotes - 1
 163             ELSE
 164                 downvotes
 165             END,
 166             controversy_rank = controversy_rank (ca.upvotes + CASE WHEN NEW.score = 1 THEN
 167                     1
 168                 ELSE
 169                     0
 170                 END::numeric, ca.downvotes + CASE WHEN NEW.score = - 1 THEN
 171                     1
 172                 ELSE
 173                     0
 174                 END::numeric)
 175         FROM
 176             comment c
 177         WHERE
 178             ca.comment_id = c.id
 179             AND ca.comment_id = OLD.comment_id;
 180     END IF;
 181     RETURN NULL;
 182 END
 183 $$;
 184