migrations/2020-12-10-152350_create_post_aggregates/up.sql

   1 -- Add post aggregates
   2 create table post_aggregates (
   3   id serial primary key,
   4   post_id int references post on update cascade on delete cascade not null,
   5   comments bigint not null default 0,
   6   score bigint not null default 0,
   7   upvotes bigint not null default 0,
   8   downvotes bigint not null default 0,
   9   stickied boolean not null default false,
  10   published timestamp not null default now(),
  11   newest_comment_time timestamp not null default now(),
  12   unique (post_id)
  13 );
  14
  15 insert into post_aggregates (post_id, comments, score, upvotes, downvotes, stickied, published, newest_comment_time)
  16   select
  17     p.id,
  18     coalesce(ct.comments, 0::bigint) as comments,
  19     coalesce(pl.score, 0::bigint) as score,
  20     coalesce(pl.upvotes, 0::bigint) as upvotes,
  21     coalesce(pl.downvotes, 0::bigint) as downvotes,
  22     p.stickied,
  23     p.published,
  24     greatest(ct.recent_comment_time, p.published) as newest_activity_time
  25   from post p
  26   left join (
  27     select comment.post_id,
  28     count(*) as comments,
  29     max(comment.published) as recent_comment_time
  30     from comment
  31     group by comment.post_id
  32   ) ct on ct.post_id = p.id
  33   left join (
  34     select post_like.post_id,
  35     sum(post_like.score) as score,
  36     sum(post_like.score) filter (where post_like.score = 1) as upvotes,
  37     -sum(post_like.score) filter (where post_like.score = '-1'::integer) as downvotes
  38     from post_like
  39     group by post_like.post_id
  40   ) pl on pl.post_id = p.id;
  41
  42 -- Add community aggregate triggers
  43
  44 -- initial post add
  45 create function post_aggregates_post()
  46 returns trigger language plpgsql
  47 as $$
  48 begin
  49   IF (TG_OP = 'INSERT') THEN
  50     insert into post_aggregates (post_id) values (NEW.id);
  51   ELSIF (TG_OP = 'DELETE') THEN
  52     delete from post_aggregates where post_id = OLD.id;
  53   END IF;
  54   return null;
  55 end $$;
  56
  57 create trigger post_aggregates_post
  58 after insert or delete on post
  59 for each row
  60 execute procedure post_aggregates_post();
  61
  62 -- comment count
  63 create function post_aggregates_comment_count()
  64 returns trigger language plpgsql
  65 as $$
  66 begin
  67   IF (TG_OP = 'INSERT') THEN
  68     update post_aggregates pa
  69     set comments = comments + 1,
  70     newest_comment_time = NEW.published
  71     where pa.post_id = NEW.post_id
  72     -- A 2 day necro-bump limit
  73     and published > ('now'::timestamp - '2 days'::interval);
  74   ELSIF (TG_OP = 'DELETE') THEN
  75     -- Join to post because that post may not exist anymore
  76     update post_aggregates pa
  77     set comments = comments - 1
  78     from post p
  79     where pa.post_id = p.id
  80     and pa.post_id = OLD.post_id;
  81   END IF;
  82   return null;
  83 end $$;
  84
  85 create trigger post_aggregates_comment_count
  86 after insert or delete on comment
  87 for each row
  88 execute procedure post_aggregates_comment_count();
  89
  90 -- post score
  91 create function post_aggregates_score()
  92 returns trigger language plpgsql
  93 as $$
  94 begin
  95   IF (TG_OP = 'INSERT') THEN
  96     update post_aggregates pa
  97     set score = score + NEW.score,
  98     upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
  99     downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
 100     where pa.post_id = NEW.post_id;
 101
 102   ELSIF (TG_OP = 'DELETE') THEN
 103     -- Join to post because that post may not exist anymore
 104     update post_aggregates pa
 105     set score = score - OLD.score,
 106     upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
 107     downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
 108     from post p
 109     where pa.post_id = p.id
 110     and pa.post_id = OLD.post_id;
 111
 112   END IF;
 113   return null;
 114 end $$;
 115
 116 create trigger post_aggregates_score
 117 after insert or delete on post_like
 118 for each row
 119 execute procedure post_aggregates_score();
 120
 121 -- post stickied
 122 create function post_aggregates_stickied()
 123 returns trigger language plpgsql
 124 as $$
 125 begin
 126   update post_aggregates pa
 127   set stickied = NEW.stickied
 128   where pa.post_id = NEW.id;
 129
 130   return null;
 131 end $$;
 132
 133 create trigger post_aggregates_stickied
 134 after update on post
 135 for each row
 136 when (OLD.stickied is distinct from NEW.stickied)
 137 execute procedure post_aggregates_stickied();