Page two, again

Sam's theory is wrong and specifically so. A saturated pg pool does not return partial results — Pool.query queues until a client frees up and, with connectionTimeoutMillis set, rejects with an error. There is no code path in node-postgres that resolves a query with a truncated row set. It also fails to explain why it is always total emptiness rather than a short page, and why it tracks block count rather than load.

1. Score the triage table, then FEED-441's repair separately — that split is where this challenge earns its place. Cause and cure are different skills and this is the one challenge in the set that scores them apart.

2. Score FEED-460 like three-bugs-and-a-lie's D: refusing it while quoting the is_pinned DESC line is the best available answer, and "fixing" the sort by removing the pinned clause is a fabricated bug fixed with a patch that breaks a shipped feature.

3. Anything else — the real extra, and it is a good one, is that the count query drops the block filter entirely: total counts comments the viewer cannot see, so the header over-reports and hasMore promises a page that comes back empty. That is a third real bug nobody filed, and a model that finds it has read past the part of the file the tickets pointed at. Others: two round trips where count(*) OVER () would do one; page is floored at 1 but never capped, so a large page number is a full scan for free; rows.rowCount is nullable in the node-postgres types.

4. Best segment: paste the NOT IN clause into psql with a NULL in the subquery and show the empty result. It takes fifteen seconds and it is the most surprising thing in the whole coding set to anyone who has not been bitten by it.

The schema

CREATE TABLE users (
  id          uuid PRIMARY KEY,
  handle      text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE threads (
  id          uuid PRIMARY KEY,
  title       text NOT NULL
);

CREATE TABLE comments (
  id          uuid PRIMARY KEY,
  thread_id   uuid NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  author_id   uuid NOT NULL REFERENCES users(id),
  body        text NOT NULL,
  is_pinned   boolean NOT NULL DEFAULT false,
  is_deleted  boolean NOT NULL DEFAULT false,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX comments_thread_created_idx
  ON comments (thread_id, created_at DESC);

CREATE TABLE blocks (
  user_id          uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  -- Nulled rather than deleted when the blocked account is removed, so the
  -- block survives an account being recreated on the same handle.
  blocked_user_id  uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at       timestamptz NOT NULL DEFAULT now()
);

The code

/**
 * feed/comments.ts — the comment feed query. Serves the thread view and the
 * mobile API. Roughly 4k requests/second at peak.
 */
import { Pool } from "pg";
import { logger } from "../logger";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const PAGE_SIZE = 20;

export interface Comment {
  id: string;
  authorHandle: string;
  body: string;
  isPinned: boolean;
  createdAt: Date;
}

export interface Page {
  comments: Comment[];
  /** Total matching this thread + viewer, for the "N comments" header. */
  total: number;
  hasMore: boolean;
}

/**
 * One page of a thread, as seen by one viewer.
 *
 * `page` is 1-based and comes straight off the query string.
 */
export async function getCommentPage(
  threadId: string,
  viewerId: string,
  page: number
): Promise<Page> {
  const offset = (Math.max(1, page) - 1) * PAGE_SIZE;

  const rows = await pool.query<{
    id: string;
    handle: string;
    body: string;
    is_pinned: boolean;
    created_at: Date;
  }>(
    `SELECT c.id, u.handle, c.body, c.is_pinned, c.created_at
       FROM comments c
       JOIN users u ON u.id = c.author_id
      WHERE c.thread_id = $1
        AND c.is_deleted = false
        AND c.author_id NOT IN (
              SELECT b.blocked_user_id
                FROM blocks b
               WHERE b.user_id = $2
            )
      ORDER BY c.is_pinned DESC, c.created_at DESC
      LIMIT $3 OFFSET $4`,
    [threadId, viewerId, PAGE_SIZE, offset]
  );

  const counted = await pool.query<{ count: string }>(
    `SELECT count(*) AS count
       FROM comments c
      WHERE c.thread_id = $1
        AND c.is_deleted = false`,
    [threadId]
  );

  const total = Number(counted.rows[0].count);

  logger.debug("feed page served", {
    threadId,
    viewerId,
    page,
    returned: rows.rowCount,
    total,
  });

  return {
    comments: rows.rows.map((r) => ({
      id: r.id,
      authorHandle: r.handle,
      body: r.body,
      isPinned: r.is_pinned,
      createdAt: r.created_at,
    })),
    total,
    hasMore: offset + rows.rowCount < total,
  };
}

Answer in exactly these sections

Triage

A table with one row per ticket, in order:

Verdict is exactly one of REAL or NOT A BUG. Cause is one line — for a real bug, what is actually wrong; for a non-bug, what the reporter is actually looking at and why the code above does not have the defect they describe.

The theory on the ticket

One paragraph on Sam's connection-pool explanation. Right, partly right, or wrong — and if wrong, say what a saturated pg pool actually does to a query, because it is not what he says it does.

The patches

For each ticket you marked REAL, and only those:

• Quote the exact line or lines that are wrong, verbatim from the code or schema above.
• Give the exact replacement.
• One line on why that fixes it.

For FEED-441 specifically, if the correct fix changes the shape of the API rather than one line, say so plainly and give the smallest version of it you would actually ship.

Do not give me the whole file back.

Anything else

At most two further defects nobody has reported, each one you can quote a line for.

If there is nothing else worth raising, write exactly Nothing else.

The standard I'm holding you to

Every claim should be checkable against the schema and the code above, or against documented PostgreSQL behavior you can state exactly. One of these bugs turns on a rule about how SQL evaluates a specific operator — if that is your diagnosis, state the rule, do not just name it. A confident diagnosis of a bug that is not here costs you more than missing one that is.