1 drop index idx_post_aggregates_newest_comment_time,
2 idx_post_aggregates_stickied_newest_comment_time,
3 idx_post_aggregates_stickied_comments;
5 alter table post_aggregates drop column newest_comment_time;
7 alter table post_aggregates rename column newest_comment_time_necro to newest_comment_time;
9 create or replace function post_aggregates_comment_count()
10 returns trigger language plpgsql
13 IF (TG_OP = 'INSERT') THEN
14 update post_aggregates pa
15 set comments = comments + 1
16 where pa.post_id = NEW.post_id;
18 -- A 2 day necro-bump limit
19 update post_aggregates pa
20 set newest_comment_time = NEW.published
21 where pa.post_id = NEW.post_id
22 and published > ('now'::timestamp - '2 days'::interval);
23 ELSIF (TG_OP = 'DELETE') THEN
24 -- Join to post because that post may not exist anymore
25 update post_aggregates pa
26 set comments = comments - 1
28 where pa.post_id = p.id
29 and pa.post_id = OLD.post_id;