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
   7     ADD COLUMN newest_comment_time timestamp NOT NULL DEFAULT now();
   8
   9 -- Set the current newest_comment_time based on the old ones
  10 UPDATE
  11     post_aggregates
  12 SET
  13     newest_comment_time = newest_comment_time_necro;
  14
  15 -- Add the indexes for this new column
  16 CREATE INDEX idx_post_aggregates_newest_comment_time ON post_aggregates (newest_comment_time DESC);
  17
  18 CREATE INDEX idx_post_aggregates_stickied_newest_comment_time ON post_aggregates (stickied DESC, newest_comment_time DESC);
  19
  20 -- Forgot to add index w/ stickied first for most comments:
  21 CREATE INDEX idx_post_aggregates_stickied_comments ON post_aggregates (stickied DESC, comments DESC);
  22
  23 -- Alter the comment trigger to set the newest_comment_time, and newest_comment_time_necro
  24 CREATE OR REPLACE FUNCTION post_aggregates_comment_count ()
  25     RETURNS TRIGGER
  26     LANGUAGE plpgsql
  27     AS $$
  28 BEGIN
  29     IF (TG_OP = 'INSERT') THEN
  30         UPDATE
  31             post_aggregates pa
  32         SET
  33             comments = comments + 1,
  34             newest_comment_time = NEW.published
  35         WHERE
  36             pa.post_id = NEW.post_id;
  37         -- A 2 day necro-bump limit
  38         UPDATE
  39             post_aggregates pa
  40         SET
  41             newest_comment_time_necro = NEW.published
  42         WHERE
  43             pa.post_id = NEW.post_id
  44             AND published > ('now'::timestamp - '2 days'::interval);
  45     ELSIF (TG_OP = 'DELETE') THEN
  46         -- Join to post because that post may not exist anymore
  47         UPDATE
  48             post_aggregates pa
  49         SET
  50             comments = comments - 1
  51         FROM
  52             post p
  53         WHERE
  54             pa.post_id = p.id
  55             AND pa.post_id = OLD.post_id;
  56     END IF;
  57     RETURN NULL;
  58 END
  59 $$;
  60