migrations/2023-07-08-101154_fix_soft_delete_aggregates/up.sql

   1 -- Fix for duplicated decrementations when both `deleted` and `removed` fields are set subsequently
   2 create or replace function was_removed_or_deleted(TG_OP text, OLD record, NEW record)
   3 RETURNS boolean
   4 LANGUAGE plpgsql
   5 as $$
   6     begin
   7         IF (TG_OP = 'INSERT') THEN
   8             return false;
   9         end if;
  10
  11         IF (TG_OP = 'DELETE' AND OLD.deleted = 'f' AND OLD.removed = 'f') THEN
  12             return true;
  13         end if;
  14
  15     return TG_OP = 'UPDATE' AND OLD.deleted = 'f' AND OLD.removed = 'f' AND (
  16             NEW.deleted = 't' OR NEW.removed = 't'
  17             );
  18 END $$;
  19
  20 create or replace function was_restored_or_created(TG_OP text, OLD record, NEW record)
  21     RETURNS boolean
  22     LANGUAGE plpgsql
  23 as $$
  24 begin
  25     IF (TG_OP = 'DELETE') THEN
  26         return false;
  27     end if;
  28
  29     IF (TG_OP = 'INSERT') THEN
  30         return true;
  31     end if;
  32
  33    return TG_OP = 'UPDATE' AND NEW.deleted = 'f' AND NEW.removed = 'f' AND (
  34             OLD.deleted = 't' OR OLD.removed = 't'
  35             );
  36 END $$;
  37
  38 -- Fix for post's comment count not updating after setting `removed` to 't'
  39 drop trigger if exists post_aggregates_comment_set_deleted on comment;
  40 drop function post_aggregates_comment_deleted();
  41
  42 create or replace function post_aggregates_comment_count()
  43     returns trigger language plpgsql
  44 as $$
  45 begin
  46     -- Check for post existence - it may not exist anymore
  47     IF TG_OP = 'INSERT' OR EXISTS (
  48         select 1 from post p where p.id = OLD.post_id
  49     ) THEN
  50         IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
  51             update post_aggregates pa
  52             set comments = comments + 1 where pa.post_id = NEW.post_id;
  53         ELSIF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
  54             update post_aggregates pa
  55             set comments = comments - 1 where pa.post_id = OLD.post_id;
  56         END IF;
  57     END IF;
  58
  59     IF TG_OP = 'INSERT' THEN
  60         update post_aggregates pa
  61         set newest_comment_time = NEW.published
  62         where pa.post_id = NEW.post_id;
  63
  64         -- A 2 day necro-bump limit
  65         update post_aggregates pa
  66         set newest_comment_time_necro = NEW.published
  67         from post p
  68         where pa.post_id = p.id
  69         and pa.post_id = NEW.post_id
  70         -- Fix issue with being able to necro-bump your own post
  71         and NEW.creator_id != p.creator_id
  72         and pa.published > ('now'::timestamp - '2 days'::interval);
  73     END IF;
  74
  75     return null;
  76 end $$;
  77
  78 create or replace trigger post_aggregates_comment_count
  79     after insert or delete or update of removed, deleted on comment
  80     for each row
  81 execute procedure post_aggregates_comment_count();