max / makenotwork

3.1 KB · 69 lines History Blame Raw

1	-- Reintroduce a denormalized per-thread post counter to make `?sort=replies`
2	-- bounded again. Migration 027 dropped the previous `reply_count` column
3	-- because it was maintained by application code that incremented on reply but
4	-- missed the decrement paths (mod-remove, soft-delete), so it drifted upward
5	-- permanently. The live replacement, a correlated `COUNT(*)` subquery per row,
6	-- is correct but turns `?sort=replies` into a full-category aggregate on a
7	-- cacheless public GET (the cheapest request for a scraper, one of the most
8	-- expensive for Postgres).
9	--
10	-- This counter is maintained by a TRIGGER, not application code. A trigger
11	-- fires on every INSERT/UPDATE/DELETE regardless of which query issued it, so
12	-- it cannot miss a mutation path the way the 022 application code did; that
13	-- missed-path bug is exactly the root cause of the prior drift. `post_count` is
14	-- the number of non-removed posts in the thread (including the OP); the display
15	-- reply count is `GREATEST(post_count - 1, 0)`, computed at read time.
16	--
17	-- No `>= 0` CHECK constraint: the 027 comment notes the old CHECK blocked the
18	-- naive decrement fix, and the read path floors with GREATEST regardless, so a
19	-- constraint would only add a failure mode without adding safety.
20
21	ALTER TABLE threads ADD COLUMN post_count INTEGER NOT NULL DEFAULT 0;
22
23	-- Backfill from current state.
24	UPDATE threads t
25	SET post_count = (
26	SELECT COUNT(*) FROM posts p
27	WHERE p.thread_id = t.id AND p.removed_at IS NULL
28	);
29
30	CREATE OR REPLACE FUNCTION threads_maintain_post_count() RETURNS TRIGGER AS $$
31	BEGIN
32	IF TG_OP = 'INSERT' THEN
33	IF NEW.removed_at IS NULL THEN
34	UPDATE threads SET post_count = post_count + 1 WHERE id = NEW.thread_id;
35	END IF;
36	RETURN NEW;
37	ELSIF TG_OP = 'DELETE' THEN
38	IF OLD.removed_at IS NULL THEN
39	UPDATE threads SET post_count = post_count - 1 WHERE id = OLD.thread_id;
40	END IF;
41	RETURN OLD;
42	ELSE -- UPDATE: react to removed_at transitions (and the unlikely thread move)
43	IF OLD.thread_id = NEW.thread_id THEN
44	IF OLD.removed_at IS NULL AND NEW.removed_at IS NOT NULL THEN
45	UPDATE threads SET post_count = post_count - 1 WHERE id = NEW.thread_id;
46	ELSIF OLD.removed_at IS NOT NULL AND NEW.removed_at IS NULL THEN
47	UPDATE threads SET post_count = post_count + 1 WHERE id = NEW.thread_id;
48	END IF;
49	ELSE
50	IF OLD.removed_at IS NULL THEN
51	UPDATE threads SET post_count = post_count - 1 WHERE id = OLD.thread_id;
52	END IF;
53	IF NEW.removed_at IS NULL THEN
54	UPDATE threads SET post_count = post_count + 1 WHERE id = NEW.thread_id;
55	END IF;
56	END IF;
57	RETURN NEW;
58	END IF;
59	END;
60	$$ LANGUAGE plpgsql;
61
62	CREATE TRIGGER trg_posts_maintain_thread_post_count
63	AFTER INSERT OR DELETE OR UPDATE OF removed_at, thread_id ON posts
64	FOR EACH ROW EXECUTE FUNCTION threads_maintain_post_count();
65
66	-- Support the `?sort=replies` ordering: pinned first, then by post_count.
67	CREATE INDEX idx_threads_category_replies
68	ON threads (category_id, pinned DESC, post_count DESC, last_activity_at DESC);
69