1 -- Forgot to add hot rank active to these two triggers
3 create or replace function refresh_post()
4 returns trigger language plpgsql
7 IF (TG_OP = 'DELETE') THEN
8 delete from post_aggregates_fast where id = OLD.id;
10 -- Update community number of posts
11 update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
12 ELSIF (TG_OP = 'UPDATE') THEN
13 delete from post_aggregates_fast where id = OLD.id;
14 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id on conflict (id) do nothing;
15 ELSIF (TG_OP = 'INSERT') THEN
16 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
18 -- Update that users number of posts, post score
19 delete from user_fast where id = NEW.creator_id;
20 insert into user_fast select * from user_view where id = NEW.creator_id on conflict (id) do nothing;
22 -- Update community number of posts
23 update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
25 -- Update the hot rank on the post table
26 -- TODO this might not correctly update it, using a 1 week interval
27 update post_aggregates_fast as paf
29 hot_rank = pav.hot_rank,
30 hot_rank_active = pav.hot_rank_active
31 from post_aggregates_view as pav
32 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
38 create or replace function refresh_comment()
39 returns trigger language plpgsql
42 IF (TG_OP = 'DELETE') THEN
43 delete from comment_aggregates_fast where id = OLD.id;
45 -- Update community number of comments
46 update community_aggregates_fast as caf
47 set number_of_comments = number_of_comments - 1
49 where caf.id = p.community_id and p.id = OLD.post_id;
51 ELSIF (TG_OP = 'UPDATE') THEN
52 delete from comment_aggregates_fast where id = OLD.id;
53 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id on conflict (id) do nothing;
54 ELSIF (TG_OP = 'INSERT') THEN
55 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
57 -- Update user view due to comment count
59 set number_of_comments = number_of_comments + 1
60 where id = NEW.creator_id;
62 -- Update post view due to comment count, new comment activity time, but only on new posts
63 -- TODO this could be done more efficiently
64 delete from post_aggregates_fast where id = NEW.post_id;
65 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id on conflict (id) do nothing;
67 -- Update the comment hot_ranks as of last week
68 update comment_aggregates_fast as caf
70 hot_rank = cav.hot_rank,
71 hot_rank_active = cav.hot_rank_active
72 from comment_aggregates_view as cav
73 where caf.id = cav.id and (cav.published > ('now'::timestamp - '1 week'::interval));
75 -- Update the post ranks
76 update post_aggregates_fast as paf
78 hot_rank = pav.hot_rank,
79 hot_rank_active = pav.hot_rank_active
80 from post_aggregates_view as pav
81 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
83 -- Force the hot rank active as zero on 2 day-older posts (necro-bump)
84 update post_aggregates_fast as paf
85 set hot_rank_active = 0
86 where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '2 days'::interval));
88 -- Update community number of comments
89 update community_aggregates_fast as caf
90 set number_of_comments = number_of_comments + 1
92 where caf.id = p.community_id and p.id = NEW.post_id;