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
   3 -- Controversy Rank:
   4 --      if downvotes <= 0 or upvotes <= 0:
   5 --          0
   6 --      else:
   7 --          (upvotes + downvotes) * min(upvotes, downvotes) / max(upvotes, downvotes)
   8 create or replace function controversy_rank(upvotes numeric, downvotes numeric)
   9 returns float as $$
  10 begin
  11     if downvotes <= 0 or upvotes <= 0 then
  12         return 0;
  13     else
  14         return (upvotes + downvotes) *
  15             case when upvotes > downvotes
  16                 then downvotes::float / upvotes::float
  17                 else upvotes::float / downvotes::float
  18             end;
  19     end if;
  20 end; $$
  21 LANGUAGE plpgsql
  22 IMMUTABLE;
  23
  24 -- Aggregates
  25 alter table post_aggregates add column controversy_rank float not null default 0;
  26 alter table comment_aggregates add column controversy_rank float not null default 0;
  27
  28 -- Populate them initially
  29 -- Note: After initial population, these are updated with vote triggers
  30 update post_aggregates set controversy_rank = controversy_rank(upvotes::numeric, downvotes::numeric);
  31 update comment_aggregates set controversy_rank = controversy_rank(upvotes::numeric, downvotes::numeric);
  32
  33 -- Create single column indexes
  34 create index idx_post_aggregates_featured_local_controversy on post_aggregates (featured_local desc, controversy_rank desc);
  35 create index idx_post_aggregates_featured_community_controversy on post_aggregates (featured_community desc, controversy_rank desc);
  36 create index idx_comment_aggregates_controversy on comment_aggregates (controversy_rank desc);
  37
  38 -- Update post_aggregates_score trigger function to include controversy_rank update
  39 create or replace function post_aggregates_score()
  40 returns trigger language plpgsql
  41 as $$
  42 begin
  43   IF (TG_OP = 'INSERT') THEN
  44     update post_aggregates pa
  45     set score = score + NEW.score,
  46     upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
  47     downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end,
  48     controversy_rank = controversy_rank(pa.upvotes + case when NEW.score = 1 then 1 else 0 end::numeric,
  49                                          pa.downvotes + case when NEW.score = -1 then 1 else 0 end::numeric)
  50     where pa.post_id = NEW.post_id;
  51
  52   ELSIF (TG_OP = 'DELETE') THEN
  53     -- Join to post because that post may not exist anymore
  54     update post_aggregates pa
  55     set score = score - OLD.score,
  56     upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
  57     downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end,
  58     controversy_rank = controversy_rank(pa.upvotes + case when NEW.score = 1 then 1 else 0 end::numeric,
  59                                          pa.downvotes + case when NEW.score = -1 then 1 else 0 end::numeric)
  60     from post p
  61     where pa.post_id = p.id
  62     and pa.post_id = OLD.post_id;
  63
  64   END IF;
  65   return null;
  66 end $$;
  67
  68 -- Update comment_aggregates_score trigger function to include controversy_rank update
  69 create or replace function comment_aggregates_score()
  70 returns trigger language plpgsql
  71 as $$
  72 begin
  73   IF (TG_OP = 'INSERT') THEN
  74     update comment_aggregates ca
  75     set score = score + NEW.score,
  76     upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
  77     downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end,
  78     controversy_rank = controversy_rank(ca.upvotes + case when NEW.score = 1 then 1 else 0 end::numeric,
  79                                          ca.downvotes + case when NEW.score = -1 then 1 else 0 end::numeric)
  80     where ca.comment_id = NEW.comment_id;
  81
  82   ELSIF (TG_OP = 'DELETE') THEN
  83     -- Join to comment because that comment may not exist anymore
  84     update comment_aggregates ca
  85     set score = score - OLD.score,
  86     upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
  87     downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end,
  88     controversy_rank = controversy_rank(ca.upvotes + case when NEW.score = 1 then 1 else 0 end::numeric,
  89                                          ca.downvotes + case when NEW.score = -1 then 1 else 0 end::numeric)
  90     from comment c
  91     where ca.comment_id = c.id
  92     and ca.comment_id = OLD.comment_id;
  93
  94   END IF;
  95   return null;
  96 end $$;
  97