migrations/2020-08-03-000110_add_preferred_usernames_banners_and_icons/up.sql

   1 -- This adds the following columns, as well as updates the views:
   2 --  Site icon
   3 --  Site banner
   4 --  Community icon
   5 --  Community Banner
   6 --  User Banner (User avatar is already there)
   7 --  User preferred name (already in table, needs to be added to view)
   8
   9 -- It also adds hot_rank_active to post_view
  10
  11 alter table site
  12   add column icon text,
  13   add column banner text;
  14
  15 alter table community
  16   add column icon text,
  17   add column banner text;
  18
  19 alter table user_ add column banner text;
  20
  21 drop view site_view;
  22 create view site_view as
  23 select s.*,
  24 u.name as creator_name,
  25 u.preferred_username as creator_preferred_username,
  26 u.avatar as creator_avatar,
  27 (select count(*) from user_) as number_of_users,
  28 (select count(*) from post) as number_of_posts,
  29 (select count(*) from comment) as number_of_comments,
  30 (select count(*) from community) as number_of_communities
  31 from site s
  32 left join user_ u on s.creator_id = u.id;
  33
  34 -- User
  35 drop table user_fast;
  36 drop view user_view;
  37 create view user_view as
  38 select
  39         u.id,
  40   u.actor_id,
  41         u.name,
  42   u.preferred_username,
  43         u.avatar,
  44   u.banner,
  45         u.email,
  46         u.matrix_user_id,
  47   u.bio,
  48   u.local,
  49         u.admin,
  50         u.banned,
  51         u.show_avatars,
  52         u.send_notifications_to_email,
  53         u.published,
  54         coalesce(pd.posts, 0) as number_of_posts,
  55         coalesce(pd.score, 0) as post_score,
  56         coalesce(cd.comments, 0) as number_of_comments,
  57         coalesce(cd.score, 0) as comment_score
  58 from user_ u
  59 left join (
  60     select
  61         p.creator_id as creator_id,
  62         count(distinct p.id) as posts,
  63         sum(pl.score) as score
  64     from post p
  65     join post_like pl on p.id = pl.post_id
  66     group by p.creator_id
  67 ) pd on u.id = pd.creator_id
  68 left join (
  69     select
  70         c.creator_id,
  71         count(distinct c.id) as comments,
  72         sum(cl.score) as score
  73     from comment c
  74     join comment_like cl on c.id = cl.comment_id
  75     group by c.creator_id
  76 ) cd on u.id = cd.creator_id;
  77
  78 create table user_fast as select * from user_view;
  79 alter table user_fast add primary key (id);
  80
  81 -- private message
  82 drop view private_message_view;
  83 create view private_message_view as
  84 select
  85 pm.*,
  86 u.name as creator_name,
  87 u.preferred_username as creator_preferred_username,
  88 u.avatar as creator_avatar,
  89 u.actor_id as creator_actor_id,
  90 u.local as creator_local,
  91 u2.name as recipient_name,
  92 u2.preferred_username as recipient_preferred_username,
  93 u2.avatar as recipient_avatar,
  94 u2.actor_id as recipient_actor_id,
  95 u2.local as recipient_local
  96 from private_message pm
  97 inner join user_ u on u.id = pm.creator_id
  98 inner join user_ u2 on u2.id = pm.recipient_id;
  99
 100 -- Post fast
 101 drop view post_fast_view;
 102 drop table post_aggregates_fast;
 103 drop view post_view;
 104 drop view post_aggregates_view;
 105
 106 create view post_aggregates_view as
 107 select
 108         p.*,
 109         -- creator details
 110         u.actor_id as creator_actor_id,
 111         u."local" as creator_local,
 112         u."name" as creator_name,
 113   u."preferred_username" as creator_preferred_username,
 114   u.published as creator_published,
 115         u.avatar as creator_avatar,
 116   u.banned as banned,
 117   cb.id::bool as banned_from_community,
 118         -- community details
 119         c.actor_id as community_actor_id,
 120         c."local" as community_local,
 121         c."name" as community_name,
 122   c.icon as community_icon,
 123         c.removed as community_removed,
 124         c.deleted as community_deleted,
 125         c.nsfw as community_nsfw,
 126         -- post score data/comment count
 127         coalesce(ct.comments, 0) as number_of_comments,
 128         coalesce(pl.score, 0) as score,
 129         coalesce(pl.upvotes, 0) as upvotes,
 130         coalesce(pl.downvotes, 0) as downvotes,
 131         hot_rank(coalesce(pl.score, 1), p.published) as hot_rank,
 132   hot_rank(coalesce(pl.score, 1), greatest(ct.recent_comment_time, p.published)) as hot_rank_active,
 133         greatest(ct.recent_comment_time, p.published) as newest_activity_time
 134 from post p
 135 left join user_ u on p.creator_id = u.id
 136 left join community_user_ban cb on p.creator_id = cb.user_id and p.community_id = cb.community_id
 137 left join community c on p.community_id = c.id
 138 left join (
 139         select
 140                 post_id,
 141                 count(*) as comments,
 142                 max(published) as recent_comment_time
 143         from comment
 144         group by post_id
 145 ) ct on ct.post_id = p.id
 146 left join (
 147         select
 148                 post_id,
 149                 sum(score) as score,
 150                 sum(score) filter (where score = 1) as upvotes,
 151                 -sum(score) filter (where score = -1) as downvotes
 152         from post_like
 153         group by post_id
 154 ) pl on pl.post_id = p.id
 155 order by p.id;
 156
 157 create view post_view as
 158 select
 159         pav.*,
 160         us.id as user_id,
 161         us.user_vote as my_vote,
 162         us.is_subbed::bool as subscribed,
 163         us.is_read::bool as read,
 164         us.is_saved::bool as saved
 165 from post_aggregates_view pav
 166 cross join lateral (
 167         select
 168                 u.id,
 169                 coalesce(cf.community_id, 0) as is_subbed,
 170                 coalesce(pr.post_id, 0) as is_read,
 171                 coalesce(ps.post_id, 0) as is_saved,
 172                 coalesce(pl.score, 0) as user_vote
 173         from user_ u
 174         left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
 175         left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
 176         left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
 177         left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
 178         left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
 179 ) as us
 180
 181 union all
 182
 183 select
 184 pav.*,
 185 null as user_id,
 186 null as my_vote,
 187 null as subscribed,
 188 null as read,
 189 null as saved
 190 from post_aggregates_view pav;
 191
 192 create table post_aggregates_fast as select * from post_aggregates_view;
 193 alter table post_aggregates_fast add primary key (id);
 194
 195 -- For the hot rank resorting
 196 create index idx_post_aggregates_fast_hot_rank_published on post_aggregates_fast (hot_rank desc, published desc);
 197 create index idx_post_aggregates_fast_hot_rank_active_published on post_aggregates_fast (hot_rank_active desc, published desc);
 198
 199 create view post_fast_view as
 200 select
 201         pav.*,
 202         us.id as user_id,
 203         us.user_vote as my_vote,
 204         us.is_subbed::bool as subscribed,
 205         us.is_read::bool as read,
 206         us.is_saved::bool as saved
 207 from post_aggregates_fast pav
 208 cross join lateral (
 209         select
 210                 u.id,
 211                 coalesce(cf.community_id, 0) as is_subbed,
 212                 coalesce(pr.post_id, 0) as is_read,
 213                 coalesce(ps.post_id, 0) as is_saved,
 214                 coalesce(pl.score, 0) as user_vote
 215         from user_ u
 216         left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
 217         left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
 218         left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
 219         left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
 220         left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
 221 ) as us
 222
 223 union all
 224
 225 select
 226 pav.*,
 227 null as user_id,
 228 null as my_vote,
 229 null as subscribed,
 230 null as read,
 231 null as saved
 232 from post_aggregates_fast pav;
 233
 234 -- Community
 235 drop view community_moderator_view;
 236 drop view community_follower_view;
 237 drop view community_user_ban_view;
 238 drop view community_view;
 239 drop view community_aggregates_view;
 240 drop view community_fast_view;
 241 drop table community_aggregates_fast;
 242
 243 create view community_aggregates_view as
 244 select
 245     c.id,
 246     c.name,
 247     c.title,
 248     c.icon,
 249     c.banner,
 250     c.description,
 251     c.category_id,
 252     c.creator_id,
 253     c.removed,
 254     c.published,
 255     c.updated,
 256     c.deleted,
 257     c.nsfw,
 258     c.actor_id,
 259     c.local,
 260     c.last_refreshed_at,
 261     u.actor_id as creator_actor_id,
 262     u.local as creator_local,
 263     u.name as creator_name,
 264     u.preferred_username as creator_preferred_username,
 265     u.avatar as creator_avatar,
 266     cat.name as category_name,
 267     coalesce(cf.subs, 0) as number_of_subscribers,
 268     coalesce(cd.posts, 0) as number_of_posts,
 269     coalesce(cd.comments, 0) as number_of_comments,
 270     hot_rank(cf.subs, c.published) as hot_rank
 271 from community c
 272 left join user_ u on c.creator_id = u.id
 273 left join category cat on c.category_id = cat.id
 274 left join (
 275     select
 276         p.community_id,
 277         count(distinct p.id) as posts,
 278         count(distinct ct.id) as comments
 279     from post p
 280     join comment ct on p.id = ct.post_id
 281     group by p.community_id
 282 ) cd on cd.community_id = c.id
 283 left join (
 284     select
 285         community_id,
 286         count(*) as subs
 287     from community_follower
 288     group by community_id
 289 ) cf on cf.community_id = c.id;
 290
 291 create view community_view as
 292 select
 293     cv.*,
 294     us.user as user_id,
 295     us.is_subbed::bool as subscribed
 296 from community_aggregates_view cv
 297 cross join lateral (
 298         select
 299                 u.id as user,
 300                 coalesce(cf.community_id, 0) as is_subbed
 301         from user_ u
 302         left join community_follower cf on u.id = cf.user_id and cf.community_id = cv.id
 303 ) as us
 304
 305 union all
 306
 307 select
 308     cv.*,
 309     null as user_id,
 310     null as subscribed
 311 from community_aggregates_view cv;
 312
 313 create view community_moderator_view as
 314 select
 315     cm.*,
 316     u.actor_id as user_actor_id,
 317     u.local as user_local,
 318     u.name as user_name,
 319     u.preferred_username as user_preferred_username,
 320     u.avatar as avatar,
 321     c.actor_id as community_actor_id,
 322     c.local as community_local,
 323     c.name as community_name,
 324     c.icon as community_icon
 325 from community_moderator cm
 326 left join user_ u on cm.user_id = u.id
 327 left join community c on cm.community_id = c.id;
 328
 329 create view community_follower_view as
 330 select
 331     cf.*,
 332     u.actor_id as user_actor_id,
 333     u.local as user_local,
 334     u.name as user_name,
 335     u.preferred_username as user_preferred_username,
 336     u.avatar as avatar,
 337     c.actor_id as community_actor_id,
 338     c.local as community_local,
 339     c.name as community_name,
 340     c.icon as community_icon
 341 from community_follower cf
 342 left join user_ u on cf.user_id = u.id
 343 left join community c on cf.community_id = c.id;
 344
 345 create view community_user_ban_view as
 346 select
 347     cb.*,
 348     u.actor_id as user_actor_id,
 349     u.local as user_local,
 350     u.name as user_name,
 351     u.preferred_username as user_preferred_username,
 352     u.avatar as avatar,
 353     c.actor_id as community_actor_id,
 354     c.local as community_local,
 355     c.name as community_name,
 356     c.icon as community_icon
 357 from community_user_ban cb
 358 left join user_ u on cb.user_id = u.id
 359 left join community c on cb.community_id = c.id;
 360
 361 -- The community fast table
 362
 363 create table community_aggregates_fast as select * from community_aggregates_view;
 364 alter table community_aggregates_fast add primary key (id);
 365
 366 create view community_fast_view as
 367 select
 368 ac.*,
 369 u.id as user_id,
 370 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
 371 from user_ u
 372 cross join (
 373   select
 374   ca.*
 375   from community_aggregates_fast ca
 376 ) ac
 377
 378 union all
 379
 380 select
 381 caf.*,
 382 null as user_id,
 383 null as subscribed
 384 from community_aggregates_fast caf;
 385
 386 -- Comments, mentions, replies
 387 drop view user_mention_view;
 388 drop view reply_fast_view;
 389 drop view comment_fast_view;
 390 drop view comment_view;
 391 drop view user_mention_fast_view;
 392 drop table comment_aggregates_fast;
 393 drop view comment_aggregates_view;
 394
 395 create view comment_aggregates_view as
 396 select
 397         ct.*,
 398         -- post details
 399         p."name" as post_name,
 400         p.community_id,
 401         -- community details
 402         c.actor_id as community_actor_id,
 403         c."local" as community_local,
 404         c."name" as community_name,
 405   c.icon as community_icon,
 406         -- creator details
 407         u.banned as banned,
 408   coalesce(cb.id, 0)::bool as banned_from_community,
 409         u.actor_id as creator_actor_id,
 410         u.local as creator_local,
 411         u.name as creator_name,
 412   u.preferred_username as creator_preferred_username,
 413   u.published as creator_published,
 414         u.avatar as creator_avatar,
 415         -- score details
 416         coalesce(cl.total, 0) as score,
 417         coalesce(cl.up, 0) as upvotes,
 418         coalesce(cl.down, 0) as downvotes,
 419         hot_rank(coalesce(cl.total, 1), p.published) as hot_rank,
 420         hot_rank(coalesce(cl.total, 1), ct.published) as hot_rank_active
 421 from comment ct
 422 left join post p on ct.post_id = p.id
 423 left join community c on p.community_id = c.id
 424 left join user_ u on ct.creator_id = u.id
 425 left join community_user_ban cb on ct.creator_id = cb.user_id and p.id = ct.post_id and p.community_id = cb.community_id
 426 left join (
 427         select
 428                 l.comment_id as id,
 429                 sum(l.score) as total,
 430                 count(case when l.score = 1 then 1 else null end) as up,
 431                 count(case when l.score = -1 then 1 else null end) as down
 432         from comment_like l
 433         group by comment_id
 434 ) as cl on cl.id = ct.id;
 435
 436 create or replace view comment_view as (
 437 select
 438         cav.*,
 439   us.user_id as user_id,
 440   us.my_vote as my_vote,
 441   us.is_subbed::bool as subscribed,
 442   us.is_saved::bool as saved
 443 from comment_aggregates_view cav
 444 cross join lateral (
 445         select
 446                 u.id as user_id,
 447                 coalesce(cl.score, 0) as my_vote,
 448     coalesce(cf.id, 0) as is_subbed,
 449     coalesce(cs.id, 0) as is_saved
 450         from user_ u
 451         left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
 452         left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
 453         left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
 454 ) as us
 455
 456 union all
 457
 458 select
 459     cav.*,
 460     null as user_id,
 461     null as my_vote,
 462     null as subscribed,
 463     null as saved
 464 from comment_aggregates_view cav
 465 );
 466
 467 create table comment_aggregates_fast as select * from comment_aggregates_view;
 468 alter table comment_aggregates_fast add primary key (id);
 469
 470 create view comment_fast_view as
 471 select
 472         cav.*,
 473   us.user_id as user_id,
 474   us.my_vote as my_vote,
 475   us.is_subbed::bool as subscribed,
 476   us.is_saved::bool as saved
 477 from comment_aggregates_fast cav
 478 cross join lateral (
 479         select
 480                 u.id as user_id,
 481                 coalesce(cl.score, 0) as my_vote,
 482     coalesce(cf.id, 0) as is_subbed,
 483     coalesce(cs.id, 0) as is_saved
 484         from user_ u
 485         left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
 486         left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
 487         left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
 488 ) as us
 489
 490 union all
 491
 492 select
 493     cav.*,
 494     null as user_id,
 495     null as my_vote,
 496     null as subscribed,
 497     null as saved
 498 from comment_aggregates_fast cav;
 499
 500 create view user_mention_view as
 501 select
 502     c.id,
 503     um.id as user_mention_id,
 504     c.creator_id,
 505     c.creator_actor_id,
 506     c.creator_local,
 507     c.post_id,
 508     c.post_name,
 509     c.parent_id,
 510     c.content,
 511     c.removed,
 512     um.read,
 513     c.published,
 514     c.updated,
 515     c.deleted,
 516     c.community_id,
 517     c.community_actor_id,
 518     c.community_local,
 519     c.community_name,
 520     c.community_icon,
 521     c.banned,
 522     c.banned_from_community,
 523     c.creator_name,
 524     c.creator_preferred_username,
 525     c.creator_avatar,
 526     c.score,
 527     c.upvotes,
 528     c.downvotes,
 529     c.hot_rank,
 530     c.hot_rank_active,
 531     c.user_id,
 532     c.my_vote,
 533     c.saved,
 534     um.recipient_id,
 535     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 536     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 537 from user_mention um, comment_view c
 538 where um.comment_id = c.id;
 539
 540 create view user_mention_fast_view as
 541 select
 542     ac.id,
 543     um.id as user_mention_id,
 544     ac.creator_id,
 545     ac.creator_actor_id,
 546     ac.creator_local,
 547     ac.post_id,
 548     ac.post_name,
 549     ac.parent_id,
 550     ac.content,
 551     ac.removed,
 552     um.read,
 553     ac.published,
 554     ac.updated,
 555     ac.deleted,
 556     ac.community_id,
 557     ac.community_actor_id,
 558     ac.community_local,
 559     ac.community_name,
 560     ac.community_icon,
 561     ac.banned,
 562     ac.banned_from_community,
 563     ac.creator_name,
 564     ac.creator_preferred_username,
 565     ac.creator_avatar,
 566     ac.score,
 567     ac.upvotes,
 568     ac.downvotes,
 569     ac.hot_rank,
 570     ac.hot_rank_active,
 571     u.id as user_id,
 572     coalesce(cl.score, 0) as my_vote,
 573     (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
 574     um.recipient_id,
 575     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 576     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 577 from user_ u
 578 cross join (
 579   select
 580   ca.*
 581   from comment_aggregates_fast ca
 582 ) ac
 583 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 584 left join user_mention um on um.comment_id = ac.id
 585
 586 union all
 587
 588 select
 589     ac.id,
 590     um.id as user_mention_id,
 591     ac.creator_id,
 592     ac.creator_actor_id,
 593     ac.creator_local,
 594     ac.post_id,
 595     ac.post_name,
 596     ac.parent_id,
 597     ac.content,
 598     ac.removed,
 599     um.read,
 600     ac.published,
 601     ac.updated,
 602     ac.deleted,
 603     ac.community_id,
 604     ac.community_actor_id,
 605     ac.community_local,
 606     ac.community_name,
 607     ac.community_icon,
 608     ac.banned,
 609     ac.banned_from_community,
 610     ac.creator_name,
 611     ac.creator_preferred_username,
 612     ac.creator_avatar,
 613     ac.score,
 614     ac.upvotes,
 615     ac.downvotes,
 616     ac.hot_rank,
 617     ac.hot_rank_active,
 618     null as user_id,
 619     null as my_vote,
 620     null as saved,
 621     um.recipient_id,
 622     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 623     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 624 from comment_aggregates_fast ac
 625 left join user_mention um on um.comment_id = ac.id
 626 ;
 627
 628 -- Do the reply_view referencing the comment_fast_view
 629 create view reply_fast_view as
 630 with closereply as (
 631     select
 632     c2.id,
 633     c2.creator_id as sender_id,
 634     c.creator_id as recipient_id
 635     from comment c
 636     inner join comment c2 on c.id = c2.parent_id
 637     where c2.creator_id != c.creator_id
 638     -- Do union where post is null
 639     union
 640     select
 641     c.id,
 642     c.creator_id as sender_id,
 643     p.creator_id as recipient_id
 644     from comment c, post p
 645     where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
 646 )
 647 select cv.*,
 648 closereply.recipient_id
 649 from comment_fast_view cv, closereply
 650 where closereply.id = cv.id
 651 ;
 652
 653 -- Adding hot rank active to the triggers
 654 create or replace function refresh_post()
 655 returns trigger language plpgsql
 656 as $$
 657 begin
 658   IF (TG_OP = 'DELETE') THEN
 659     delete from post_aggregates_fast where id = OLD.id;
 660
 661     -- Update community number of posts
 662     update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
 663   ELSIF (TG_OP = 'UPDATE') THEN
 664     delete from post_aggregates_fast where id = OLD.id;
 665     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
 666   ELSIF (TG_OP = 'INSERT') THEN
 667     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
 668
 669     -- Update that users number of posts, post score
 670     delete from user_fast where id = NEW.creator_id;
 671     insert into user_fast select * from user_view where id = NEW.creator_id;
 672
 673     -- Update community number of posts
 674     update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
 675
 676     -- Update the hot rank on the post table
 677     -- TODO this might not correctly update it, using a 1 week interval
 678     update post_aggregates_fast as paf
 679     set
 680       hot_rank = pav.hot_rank,
 681       hot_rank_active = pav.hot_rank_active
 682     from post_aggregates_view as pav
 683     where paf.id = pav.id  and (pav.published > ('now'::timestamp - '1 week'::interval));
 684   END IF;
 685
 686   return null;
 687 end $$;
 688
 689 create or replace function refresh_comment()
 690 returns trigger language plpgsql
 691 as $$
 692 begin
 693   IF (TG_OP = 'DELETE') THEN
 694     delete from comment_aggregates_fast where id = OLD.id;
 695
 696     -- Update community number of comments
 697     update community_aggregates_fast as caf
 698     set number_of_comments = number_of_comments - 1
 699     from post as p
 700     where caf.id = p.community_id and p.id = OLD.post_id;
 701
 702   ELSIF (TG_OP = 'UPDATE') THEN
 703     delete from comment_aggregates_fast where id = OLD.id;
 704     insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
 705   ELSIF (TG_OP = 'INSERT') THEN
 706     insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
 707
 708     -- Update user view due to comment count
 709     update user_fast
 710     set number_of_comments = number_of_comments + 1
 711     where id = NEW.creator_id;
 712
 713     -- Update post view due to comment count, new comment activity time, but only on new posts
 714     -- TODO this could be done more efficiently
 715     delete from post_aggregates_fast where id = NEW.post_id;
 716     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id;
 717
 718     -- Update the comment hot_ranks as of last week
 719     update comment_aggregates_fast as caf
 720     set
 721       hot_rank = cav.hot_rank,
 722       hot_rank_active = cav.hot_rank_active
 723     from comment_aggregates_view as cav
 724     where caf.id = cav.id and (cav.published > ('now'::timestamp - '1 week'::interval));
 725
 726     -- Update the post ranks
 727     update post_aggregates_fast as paf
 728     set
 729       hot_rank = pav.hot_rank,
 730       hot_rank_active = pav.hot_rank_active
 731     from post_aggregates_view as pav
 732     where paf.id = pav.id  and (pav.published > ('now'::timestamp - '1 week'::interval));
 733
 734     -- Force the hot rank active as zero on 2 day-older posts (necro-bump)
 735     update post_aggregates_fast as paf
 736     set hot_rank_active = 0
 737     where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '2 days'::interval));
 738
 739     -- Update community number of comments
 740     update community_aggregates_fast as caf
 741     set number_of_comments = number_of_comments + 1
 742     from post as p
 743     where caf.id = p.community_id and p.id = NEW.post_id;
 744
 745   END IF;
 746
 747   return null;
 748 end $$;