Enforcing "at most N answers per question"

capped_answer_submit.sql

-- Cap-enforced answer submit: at most `max_answers` answers per question,
-- at most one answer per (question, user).
--
-- One transaction = the counter and the answers table cannot diverge;
-- a crash mid-way rolls back, nothing leaks. The row lock taken by the
-- UPDATE is the linearization point -- READ COMMITTED is sufficient,
-- because the WHERE clause re-evaluates after lock acquisition.
--
-- INSERT goes first on purpose: a duplicate user bounces off the PK
-- without ever touching the hot question row, and a retry after an
-- ambiguous commit resolves to "already landed" even if the question
-- filled up in between.

BEGIN;

INSERT INTO answers (question_id, user_id, body)
VALUES ($1, $2, $3);
-- 23505 unique_violation on PK (question_id, user_id) -> UserAlreadyAnswered
-- 23503 fk_violation on question_id                   -> UnknownQuestion

UPDATE questions
   SET answer_count = answer_count + 1
 WHERE id = $1
   AND answer_count < max_answers;
-- rowcount = 0 -> QuestionFull; ROLLBACK undoes the INSERT too,
-- so a rejected answer never half-exists. This is exactly why
-- both statements share one transaction.

COMMIT;

-- Error mapping (application side):
--   23505 on INSERT       -> UserAlreadyAnswered (final; if this is a retry, treat as success)
--   23503 on INSERT       -> UnknownQuestion     (final, do not retry)
--   rowcount 0 on UPDATE  -> QuestionFull        (final, do not retry)
--   connection / 40001    -> safe to retry the whole transaction