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     IF (TG_OP = 'DELETE' AND OLD.deleted = 'f' AND OLD.removed = 'f') THEN
  11         RETURN TRUE;
  12     END IF;
  13     RETURN TG_OP = 'UPDATE'
  14         AND OLD.deleted = 'f'
  15         AND OLD.removed = 'f'
  16         AND (NEW.deleted = 't'
  17             OR NEW.removed = 't');
  18 END
  19 $$;
  20
  21 CREATE OR REPLACE FUNCTION was_restored_or_created (TG_OP text, OLD record, NEW record)
  22     RETURNS boolean
  23     LANGUAGE plpgsql
  24     AS $$
  25 BEGIN
  26     IF (TG_OP = 'DELETE') THEN
  27         RETURN FALSE;
  28     END IF;
  29     IF (TG_OP = 'INSERT') THEN
  30         RETURN TRUE;
  31     END IF;
  32     RETURN TG_OP = 'UPDATE'
  33         AND NEW.deleted = 'f'
  34         AND NEW.removed = 'f'
  35         AND (OLD.deleted = 't'
  36             OR OLD.removed = 't');
  37 END
  38 $$;
  39
  40 -- Fix for post's comment count not updating after setting `removed` to 't'
  41 DROP TRIGGER IF EXISTS post_aggregates_comment_set_deleted ON comment;
  42
  43 DROP FUNCTION post_aggregates_comment_deleted ();
  44
  45 CREATE OR REPLACE FUNCTION post_aggregates_comment_count ()
  46     RETURNS TRIGGER
  47     LANGUAGE plpgsql
  48     AS $$
  49 BEGIN
  50     -- Check for post existence - it may not exist anymore
  51     IF TG_OP = 'INSERT' OR EXISTS (
  52         SELECT
  53             1
  54         FROM
  55             post p
  56         WHERE
  57             p.id = OLD.post_id) THEN
  58         IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
  59             UPDATE
  60                 post_aggregates pa
  61             SET
  62                 comments = comments + 1
  63             WHERE
  64                 pa.post_id = NEW.post_id;
  65         ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
  66             UPDATE
  67                 post_aggregates pa
  68             SET
  69                 comments = comments - 1
  70             WHERE
  71                 pa.post_id = OLD.post_id;
  72         END IF;
  73     END IF;
  74     IF TG_OP = 'INSERT' THEN
  75         UPDATE
  76             post_aggregates pa
  77         SET
  78             newest_comment_time = NEW.published
  79         WHERE
  80             pa.post_id = NEW.post_id;
  81         -- A 2 day necro-bump limit
  82         UPDATE
  83             post_aggregates pa
  84         SET
  85             newest_comment_time_necro = NEW.published
  86         FROM
  87             post p
  88         WHERE
  89             pa.post_id = p.id
  90             AND pa.post_id = NEW.post_id
  91             -- Fix issue with being able to necro-bump your own post
  92             AND NEW.creator_id != p.creator_id
  93             AND pa.published > ('now'::timestamp - '2 days'::interval);
  94     END IF;
  95     RETURN NULL;
  96 END
  97 $$;
  98
  99 CREATE OR REPLACE TRIGGER post_aggregates_comment_count
 100     AFTER INSERT OR DELETE OR UPDATE OF removed,
 101     deleted ON comment
 102     FOR EACH ROW
 103     EXECUTE PROCEDURE post_aggregates_comment_count ();
 104