1 -- This adds on conflict do nothing triggers to all the insert_intos
2 -- Github issue: https://github.com/LemmyNet/lemmy/issues/1179
4 create or replace function refresh_community()
5 returns trigger language plpgsql
8 IF (TG_OP = 'DELETE') THEN
9 delete from community_aggregates_fast where id = OLD.id;
10 ELSIF (TG_OP = 'UPDATE') THEN
11 delete from community_aggregates_fast where id = OLD.id;
12 insert into community_aggregates_fast select * from community_aggregates_view where id = NEW.id on conflict (id) do nothing;
14 -- Update user view due to owner changes
15 delete from user_fast where id = NEW.creator_id;
16 insert into user_fast select * from user_view where id = NEW.creator_id on conflict (id) do nothing;
18 -- Update post view due to community changes
19 delete from post_aggregates_fast where community_id = NEW.id;
20 insert into post_aggregates_fast select * from post_aggregates_view where community_id = NEW.id on conflict (id) do nothing;
22 -- TODO make sure this shows up in the users page ?
23 ELSIF (TG_OP = 'INSERT') THEN
24 insert into community_aggregates_fast select * from community_aggregates_view where id = NEW.id;
30 create or replace function refresh_user()
31 returns trigger language plpgsql
34 IF (TG_OP = 'DELETE') THEN
35 delete from user_fast where id = OLD.id;
36 ELSIF (TG_OP = 'UPDATE') THEN
37 delete from user_fast where id = OLD.id;
38 insert into user_fast select * from user_view where id = NEW.id on conflict(id) do nothing;
40 -- Refresh post_fast, cause of user info changes
41 delete from post_aggregates_fast where creator_id = NEW.id;
42 insert into post_aggregates_fast select * from post_aggregates_view where creator_id = NEW.id on conflict (id) do nothing;
44 delete from comment_aggregates_fast where creator_id = NEW.id;
45 insert into comment_aggregates_fast select * from comment_aggregates_view where creator_id = NEW.id on conflict (id) do nothing;
47 ELSIF (TG_OP = 'INSERT') THEN
48 insert into user_fast select * from user_view where id = NEW.id;
54 create or replace function refresh_post()
55 returns trigger language plpgsql
58 IF (TG_OP = 'DELETE') THEN
59 delete from post_aggregates_fast where id = OLD.id;
61 -- Update community number of posts
62 update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
63 ELSIF (TG_OP = 'UPDATE') THEN
64 delete from post_aggregates_fast where id = OLD.id;
65 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id on conflict (id) do nothing;
67 ELSIF (TG_OP = 'INSERT') THEN
68 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
70 -- Update that users number of posts, post score
71 delete from user_fast where id = NEW.creator_id;
72 insert into user_fast select * from user_view where id = NEW.creator_id on conflict (id) do nothing;
74 -- Update community number of posts
75 update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
77 -- Update the hot rank on the post table
78 -- TODO this might not correctly update it, using a 1 week interval
79 update post_aggregates_fast as paf
80 set hot_rank = pav.hot_rank
81 from post_aggregates_view as pav
82 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
89 create or replace function refresh_comment()
90 returns trigger language plpgsql
93 IF (TG_OP = 'DELETE') THEN
94 delete from comment_aggregates_fast where id = OLD.id;
96 -- Update community number of comments
97 update community_aggregates_fast as caf
98 set number_of_comments = number_of_comments - 1
100 where caf.id = p.community_id and p.id = OLD.post_id;
102 ELSIF (TG_OP = 'UPDATE') THEN
103 delete from comment_aggregates_fast where id = OLD.id;
104 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id on conflict (id) do nothing;
105 ELSIF (TG_OP = 'INSERT') THEN
106 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
108 -- Update user view due to comment count
110 set number_of_comments = number_of_comments + 1
111 where id = NEW.creator_id;
113 -- Update post view due to comment count, new comment activity time, but only on new posts
114 -- TODO this could be done more efficiently
115 delete from post_aggregates_fast where id = NEW.post_id;
116 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id on conflict (id) do nothing;
118 -- Force the hot rank as zero on week-older posts
119 update post_aggregates_fast as paf
121 where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '1 week'::interval));
123 -- Update community number of comments
124 update community_aggregates_fast as caf
125 set number_of_comments = number_of_comments + 1
127 where caf.id = p.community_id and p.id = NEW.post_id;