max / makenotwork

3.0 KB · 68 lines History Blame Raw

1	-- Harden the thread post_count trigger (migration 029) against the other
2	-- soft-delete column. Posts carry two: `removed_at` (mod-remove, migration 011)
3	-- and `deleted_at` (author soft-delete, migration 007). The 029 trigger only
4	-- watched `removed_at`, so the day a feature starts setting `posts.deleted_at`
5	-- (a "delete my own post" path), post_count would silently overcount, the exact
6	-- drift class 027/029 were written to kill, just keyed on the dormant column.
7	--
8	-- A post counts toward post_count iff it is active: BOTH columns null. The
9	-- trigger now reacts to transitions on either column. `deleted_at` is currently
10	-- null for every post, so the backfill is a no-op today; this is purely a guard
11	-- so the counter stays correct ahead of any future user-delete feature.
12	--
13	-- Migration hygiene: CREATE OR REPLACE for the function, DROP ... IF EXISTS
14	-- before recreating the trigger so a partial re-run can't hard-fail.
15
16	CREATE OR REPLACE FUNCTION threads_maintain_post_count() RETURNS TRIGGER AS $$
17	DECLARE
18	old_active BOOLEAN;
19	new_active BOOLEAN;
20	BEGIN
21	IF TG_OP = 'INSERT' THEN
22	IF NEW.removed_at IS NULL AND NEW.deleted_at IS NULL THEN
23	UPDATE threads SET post_count = post_count + 1 WHERE id = NEW.thread_id;
24	END IF;
25	RETURN NEW;
26	ELSIF TG_OP = 'DELETE' THEN
27	IF OLD.removed_at IS NULL AND OLD.deleted_at IS NULL THEN
28	UPDATE threads SET post_count = post_count - 1 WHERE id = OLD.thread_id;
29	END IF;
30	RETURN OLD;
31	ELSE -- UPDATE: react to active-state transitions (and the unlikely thread move)
32	old_active := (OLD.removed_at IS NULL AND OLD.deleted_at IS NULL);
33	new_active := (NEW.removed_at IS NULL AND NEW.deleted_at IS NULL);
34	IF OLD.thread_id = NEW.thread_id THEN
35	IF old_active AND NOT new_active THEN
36	UPDATE threads SET post_count = post_count - 1 WHERE id = NEW.thread_id;
37	ELSIF NOT old_active AND new_active THEN
38	UPDATE threads SET post_count = post_count + 1 WHERE id = NEW.thread_id;
39	END IF;
40	ELSE
41	IF old_active THEN
42	UPDATE threads SET post_count = post_count - 1 WHERE id = OLD.thread_id;
43	END IF;
44	IF new_active THEN
45	UPDATE threads SET post_count = post_count + 1 WHERE id = NEW.thread_id;
46	END IF;
47	END IF;
48	RETURN NEW;
49	END IF;
50	END;
51	$$ LANGUAGE plpgsql;
52
53	-- Recreate the trigger so it also fires on deleted_at changes.
54	DROP TRIGGER IF EXISTS trg_posts_maintain_thread_post_count ON posts;
55	CREATE TRIGGER trg_posts_maintain_thread_post_count
56	AFTER INSERT OR DELETE OR UPDATE OF removed_at, deleted_at, thread_id ON posts
57	FOR EACH ROW EXECUTE FUNCTION threads_maintain_post_count();
58
59	-- Re-backfill against the active definition (both columns null). No-op while
60	-- deleted_at is unused, but keeps the column authoritative if that changes.
61	UPDATE threads t
62	SET post_count = (
63	SELECT COUNT(*) FROM posts p
64	WHERE p.thread_id = t.id
65	AND p.removed_at IS NULL
66	AND p.deleted_at IS NULL
67	);
68