1 -- This adds on conflict do nothing triggers to all the insert_intos
2 -- Github issue: https://github.com/LemmyNet/lemmy/issues/1179
3 CREATE OR REPLACE FUNCTION refresh_community ()
8 IF (TG_OP = 'DELETE') THEN
9 DELETE FROM community_aggregates_fast
11 ELSIF (TG_OP = 'UPDATE') THEN
12 DELETE FROM community_aggregates_fast
14 INSERT INTO community_aggregates_fast
18 community_aggregates_view
23 -- Update user view due to owner changes
25 WHERE id = NEW.creator_id;
35 -- Update post view due to community changes
36 DELETE FROM post_aggregates_fast
37 WHERE community_id = NEW.id;
38 INSERT INTO post_aggregates_fast
47 -- TODO make sure this shows up in the users page ?
48 ELSIF (TG_OP = 'INSERT') THEN
49 INSERT INTO community_aggregates_fast
53 community_aggregates_view
61 CREATE OR REPLACE FUNCTION refresh_user ()
66 IF (TG_OP = 'DELETE') THEN
69 ELSIF (TG_OP = 'UPDATE') THEN
81 -- Refresh post_fast, cause of user info changes
82 DELETE FROM post_aggregates_fast
83 WHERE creator_id = NEW.id;
84 INSERT INTO post_aggregates_fast
93 DELETE FROM comment_aggregates_fast
94 WHERE creator_id = NEW.id;
95 INSERT INTO comment_aggregates_fast
99 comment_aggregates_view
104 ELSIF (TG_OP = 'INSERT') THEN
105 INSERT INTO user_fast
117 CREATE OR REPLACE FUNCTION refresh_post ()
122 IF (TG_OP = 'DELETE') THEN
123 DELETE FROM post_aggregates_fast
125 -- Update community number of posts
127 community_aggregates_fast
129 number_of_posts = number_of_posts - 1
131 id = OLD.community_id;
132 ELSIF (TG_OP = 'UPDATE') THEN
133 DELETE FROM post_aggregates_fast
135 INSERT INTO post_aggregates_fast
144 ELSIF (TG_OP = 'INSERT') THEN
145 INSERT INTO post_aggregates_fast
152 -- Update that users number of posts, post score
153 DELETE FROM user_fast
154 WHERE id = NEW.creator_id;
155 INSERT INTO user_fast
164 -- Update community number of posts
166 community_aggregates_fast
168 number_of_posts = number_of_posts + 1
170 id = NEW.community_id;
171 -- Update the hot rank on the post table
172 -- TODO this might not correctly update it, using a 1 week interval
174 post_aggregates_fast AS paf
176 hot_rank = pav.hot_rank
178 post_aggregates_view AS pav
181 AND (pav.published > ('now'::timestamp - '1 week'::interval));
187 CREATE OR REPLACE FUNCTION refresh_comment ()
192 IF (TG_OP = 'DELETE') THEN
193 DELETE FROM comment_aggregates_fast
195 -- Update community number of comments
197 community_aggregates_fast AS caf
199 number_of_comments = number_of_comments - 1
203 caf.id = p.community_id
204 AND p.id = OLD.post_id;
205 ELSIF (TG_OP = 'UPDATE') THEN
206 DELETE FROM comment_aggregates_fast
208 INSERT INTO comment_aggregates_fast
212 comment_aggregates_view
217 ELSIF (TG_OP = 'INSERT') THEN
218 INSERT INTO comment_aggregates_fast
222 comment_aggregates_view
225 -- Update user view due to comment count
229 number_of_comments = number_of_comments + 1
232 -- Update post view due to comment count, new comment activity time, but only on new posts
233 -- TODO this could be done more efficiently
234 DELETE FROM post_aggregates_fast
235 WHERE id = NEW.post_id;
236 INSERT INTO post_aggregates_fast
245 -- Force the hot rank as zero on week-older posts
247 post_aggregates_fast AS paf
252 AND (paf.published < ('now'::timestamp - '1 week'::interval));
253 -- Update community number of comments
255 community_aggregates_fast AS caf
257 number_of_comments = number_of_comments + 1
261 caf.id = p.community_id
262 AND p.id = NEW.post_id;