migrations/2021-02-10-164051_add_new_comments_sort_index/up.sql

   1 -- First rename current newest comment time to newest_comment_time_necro
   2 -- necro means that time is limited to 2 days, whereas newest_comment_time ignores that.
   3 alter table post_aggregates rename column newest_comment_time to newest_comment_time_necro;
   4
   5 -- Add the newest_comment_time column
   6 alter table post_aggregates add column newest_comment_time timestamp not null default now();
   7
   8 -- Set the current newest_comment_time based on the old ones
   9 update post_aggregates set newest_comment_time = newest_comment_time_necro;
  10
  11 -- Add the indexes for this new column
  12 create index idx_post_aggregates_newest_comment_time on post_aggregates (newest_comment_time desc);
  13 create index idx_post_aggregates_stickied_newest_comment_time on post_aggregates (stickied desc, newest_comment_time desc);
  14
  15 -- Forgot to add index w/ stickied first for most comments:
  16 create index idx_post_aggregates_stickied_comments on post_aggregates (stickied desc, comments desc);
  17
  18 -- Alter the comment trigger to set the newest_comment_time, and newest_comment_time_necro
  19 create or replace function post_aggregates_comment_count()
  20 returns trigger language plpgsql
  21 as $$
  22 begin
  23   IF (TG_OP = 'INSERT') THEN
  24     update post_aggregates pa
  25     set comments = comments + 1,
  26     newest_comment_time = NEW.published
  27     where pa.post_id = NEW.post_id;
  28
  29     -- A 2 day necro-bump limit
  30     update post_aggregates pa
  31     set newest_comment_time_necro = NEW.published
  32     where pa.post_id = NEW.post_id
  33     and published > ('now'::timestamp - '2 days'::interval);
  34   ELSIF (TG_OP = 'DELETE') THEN
  35     -- Join to post because that post may not exist anymore
  36     update post_aggregates pa
  37     set comments = comments - 1
  38     from post p
  39     where pa.post_id = p.id
  40     and pa.post_id = OLD.post_id;
  41   END IF;
  42   return null;
  43 end $$;