max / makenotwork

5.9 KB · 114 lines History Blame Raw

1	-- The Postgres index over refs/notes/*.
2	--
3	-- The repo is truth. Every row here is a projection of an object that exists in
4	-- a bare repository on disk, and the whole table can be dropped and rebuilt from
5	-- those repositories (`mnw-admin reindex-notes`). Nothing may be stored here and
6	-- nowhere else: a creator who leaves takes their annotations because the
7	-- annotations were never ours. Wiki: mnw-server-git-notes, "the load-bearing
8	-- rule".
9	--
10	-- What the index buys is the reads the tree cannot answer cheaply: full-text
11	-- search, an "annotated commits" filter, a feed ordered by when annotations were
12	-- written, and per-commit counts on a log page without flattening a tree. The
13	-- tip-keyed cache in src/git/notes covers a warm repository; it does not cover
14	-- searching one, and it cannot order anything by time without reading a commit
15	-- header per note.
16	--
17	-- VISIBILITY IS NOT STORED. A note in a repository inherits the repository's
18	-- visibility because it lives there; a row in a table inherits nothing. Every
19	-- read path joins to git_repos and re-checks, and no column here may be allowed
20	-- to substitute for that check.
21
22	CREATE TABLE IF NOT EXISTS git_notes (
23	repo_id UUID NOT NULL REFERENCES git_repos(id) ON DELETE CASCADE,
24
25	-- Namespace as a person says it: `commits`, `mnw/builds`. Not the full ref.
26	namespace TEXT NOT NULL,
27
28	-- The annotated object, and the blob holding the note. Hex, so 40 characters
29	-- today and 64 in a SHA-256 repository; the notes layer is hash-agnostic and
30	-- this column has no business being narrower than it is.
31	target_oid TEXT NOT NULL,
32	blob_oid TEXT NOT NULL,
33
34	-- The note, decoded lossily. Notes are conventionally UTF-8 and nothing
35	-- enforces it, so a note holding arbitrary bytes indexes as replacement
36	-- characters here while the repository keeps the bytes it was given. Search
37	-- over a mangled copy is the right trade; storing bytea would make the
38	-- tsvector impossible and buy a fidelity nobody reads this table for.
39	content TEXT NOT NULL,
40
41	-- What the annotated object turned out to be, resolved once at index time.
42	-- Notes annotate blobs and trees too, and the "annotated commits" filter is
43	-- this column: without it the filter would mean re-opening the repository to
44	-- ask what each target is.
45	target_is_commit BOOLEAN NOT NULL DEFAULT FALSE,
46	-- Subject line and commit time of the target, empty and NULL when it is not
47	-- a commit. Denormalized so the feed is one query rather than one commit
48	-- header read per row, which is what bounds the repo-backed feed today.
49	target_summary TEXT NOT NULL DEFAULT '',
50	target_time TIMESTAMPTZ,
51
52	-- When the annotation was written, and by whom, taken from the notes commit
53	-- that carried it. A full rebuild has no per-note history to consult without
54	-- walking the notes ref once per note, so it stamps every row with the tip's
55	-- own commit time: rebuilding compresses the feed's timeline, and never
56	-- reorders it against the repository, because the ref's history is gone
57	-- either way once the rows are rewritten.
58	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
59	updated_by TEXT NOT NULL DEFAULT '',
60
61	-- Search. `left(...)` because to_tsvector rejects input past 1MB and a
62	-- pushed note has no size limit (the browser write path caps at 50k chars;
63	-- a push does not go through it). Truncating the vector loses the tail of a
64	-- pathological note from search results, which beats failing its insert and
65	-- leaving the namespace unindexed.
66	--
67	-- No setweight: there is one field, so weighting it would rank nothing
68	-- against nothing. to_tsvector's config is named explicitly because the bare
69	-- form reads default_text_search_config and is not IMMUTABLE, which a
70	-- generated column requires.
71	search_tsv tsvector GENERATED ALWAYS AS (
72	to_tsvector('english', left(content, 200000))
73	) STORED,
74
75	PRIMARY KEY (repo_id, namespace, target_oid)
76	);
77
78	CREATE INDEX IF NOT EXISTS idx_git_notes_search ON git_notes USING GIN (search_tsv);
79
80	-- The feed and the atom feed: newest annotation first, within one repository.
81	CREATE INDEX IF NOT EXISTS idx_git_notes_feed ON git_notes (repo_id, updated_at DESC);
82
83	-- The log page asking "which of these commits carry a note", across namespaces.
84	-- The primary key leads with repo_id and namespace, so it cannot serve this.
85	CREATE INDEX IF NOT EXISTS idx_git_notes_target ON git_notes (repo_id, target_oid);
86
87	-- What the index has already seen, per namespace.
88	--
89	-- Two jobs, and the second is the reason this is a table rather than a column.
90	--
91	-- 1. The reindex diffs the tip it last indexed against the tip the ref holds
92	-- now, so a push walks what changed rather than the whole namespace. The
93	-- post-receive hook knows the previous tip, but the other two writers do not:
94	-- the inbox merge and the browser write both move refs/notes/* in-process and
95	-- fire no hook. Reading the previous tip from here rather than being told it
96	-- makes all three call sites the same call, and makes a reindex that ran
97	-- twice a no-op instead of a rewalk.
98	--
99	-- 2. It distinguishes "this namespace has no notes" from "this namespace has
100	-- never been indexed". A read path cannot tell those apart from an empty
101	-- result, and answering a cold index with "no notes" would make a repository
102	-- look unannotated until somebody pushed to it. Absent row means cold, and
103	-- the read falls back to walking the repository.
104	CREATE TABLE IF NOT EXISTS git_notes_index_state (
105	repo_id UUID NOT NULL REFERENCES git_repos(id) ON DELETE CASCADE,
106	namespace TEXT NOT NULL,
107	-- The notes ref tip the rows in git_notes were built from. Hex, never NULL:
108	-- a namespace whose ref is gone has its state row deleted along with its
109	-- notes, so a row here always names a commit.
110	indexed_tip TEXT NOT NULL,
111	indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
112	PRIMARY KEY (repo_id, namespace)
113	);
114