max / makenotwork

2.2 KB · 38 lines History Blame Raw

1	-- One canonical "is this post live" predicate, so it cannot be half-written.
2	--
3	-- Posts carry two soft-delete columns: `removed_at` (mod-remove, migration 011)
4	-- and `deleted_at` (author soft-delete, migration 007). A post is live iff BOTH
5	-- are null, and every read that shows posts to a user has to say so. That pair
6	-- was hand-copied at each site, and five of them drifted: the search CTE, the
7	-- tracked-thread unread count, the tracked-thread mention flag, the quote
8	-- loader, and the thread-list mention badge. Two were wrong in production
9	-- behaviour rather than merely latent. Migration 031 fixed the same class in the
10	-- post_count trigger for the same reason.
11	--
12	-- The failure mode is specifically "wrote one predicate, forgot the other", so
13	-- the fix is to make that unrepresentable: one generated column, one token at
14	-- the call site. Postgres rejects any direct write to a generated column, so it
15	-- cannot drift from its two sources the way a copied predicate can.
16	--
17	-- `deleted_at` is still dormant (nothing sets it, see 031), so this changes no
18	-- query result today. It is the guard that makes the day it stops being dormant
19	-- uneventful.
20
21	ALTER TABLE posts ADD COLUMN is_active BOOLEAN
22	GENERATED ALWAYS AS (removed_at IS NULL AND deleted_at IS NULL) STORED;
23
24	-- Mirrors idx_posts_not_removed's shape (thread_id, created_at) so the swept
25	-- reads keep an index to sit on; verified the planner picks this one for an
26	-- `is_active` predicate rather than the old partial index.
27	CREATE INDEX idx_posts_active ON posts (thread_id, created_at) WHERE is_active;
28
29	-- idx_posts_not_removed is deliberately left in place, and it is worth being
30	-- precise about why, because after this sweep no query filters
31	-- `removed_at IS NULL` any more. What still does is the write guards
32	-- (auto_hide_if_threshold_met, mod_remove_post_cascade, restore_post_cascade),
33	-- all of them `WHERE id = $1 AND removed_at IS NULL`, which a populated table
34	-- should serve from posts_pkey rather than from this index. So it is probably
35	-- dead weight on every post write. "Probably" is the reason it stays: settling
36	-- it wants pg_stat_user_indexes off production, not a planner guess against an
37	-- empty dev table, and dropping a live index is not something to do on a hunch.
38