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;
5 -- Add the newest_comment_time column
6 alter table post_aggregates add column newest_comment_time timestamp not null default now();
8 -- Set the current newest_comment_time based on the old ones
9 update post_aggregates set newest_comment_time = newest_comment_time_necro;
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);
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);
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
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;
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
39 where pa.post_id = p.id
40 and pa.post_id = OLD.post_id;