In the opening comment of infra/migrations/0055_work_code_case_uniq.sql there’s a passage that was bought with a real production outage. Before I wrote it, I first wrote down a judgment — and production shot that judgment down…

Let me lay out the background first, otherwise that comment just reads like generic hand-wringing.

What we’re fixing isn’t “can’t find it”

0001_init.sql hands work.code a bare TEXT UNIQUE, i.e. UNIQUE (code), case-sensitive. But every read path on the site matches on lower(code) = lower($1).

The consequence of that mismatch isn’t “can’t find it” — it’s one title splitting into two rows in the database. In the 2026-08 batch the catalog pusher started sending the code as DASS-966 instead of dass-966; upsert_work's ON CONFLICT (code) didn’t match, so a new row got inserted. In production, three pairs actually split: DASS-966, DSOD-009, START-597 — merged by hand on 2026-08-29.

The symptoms after a split aren’t errors either; they’re two classes of silent failure:

  1. Half the data. The old row holds source_link, work_embedding, frame_embedding; the new row has only metadata. And ... WHERE lower(w.code)=lower($1) LIMIT 1 has no ORDER BY, so which row you get is up to the query plan. At the time, DSOD-009 and START-597 were getting the row with no play sources — the watch page returned 200 with not a single source.
  2. A flat 500. Any scalar subquery of the shape = (SELECT ... WHERE lower(code)=lower($1)) raises more than one row returned by a subquery used as an expression when it hits multiple rows. Unfavorite, remove-from-playlist, and the similar-titles fallback all have that shape.

At the time, 0046_anime_import.sql:117-131 deliberately didn’t create the unique index right away, on the grounds that the existing dirty data would blow up the entire --single-transaction migration. Now that the data is merged clean, 0055 is here to tighten things up.

Why keep that case-sensitive constraint

What we’re creating is CREATE UNIQUE INDEX work_code_lower_key ON work (lower(code)). So can the old work_code_key (UNIQUE (code)) just be dropped along the way?

No. external_source_map.code is a foreign key pointing at work(code), and a foreign key requires a unique constraint on the referenced column, so DROP work_code_key would fail on dependency. And UNIQUE (lower(code)) is strictly stronger than it, so keeping it has no downside.

The non-unique functional index idx_work_lower_code created by 0046 can go, though — same columns, fully covered by the new unique index; keeping it just adds write amplification.

First judgment call: why it’s all wrapped in one DO block

Because LOCK TABLE only works inside a transaction block.

The production migration goes through psql --single-transaction in migrate.sh, which has a transaction block — no problem. But the local procedure recorded in tools/ugc-smoke/README.md is per-file psql -f, autocommit, no transaction block — a bare LOCK there just errors with LOCK TABLE can only be used in transaction blocks, and the local smoke DB ends up missing this unique constraint, which is the one and only thing this migration does.

A DO block carries its own implicit transaction, so it holds under both procedures. That also explains why LOCK and CREATE INDEX can’t be split into two statements.

Why grab the strongest lock right at the start

CREATE UNIQUE INDEX takes a SHARE lock, which blocks writes; DROP INDEX needs ACCESS EXCLUSIVE. Create-then-drop means a second lock upgrade inside the same transaction.

An online write that squeezes in between (the UPDATE work in increment_view) can’t get RowExclusive and queues; we then wait for it to yield; a cycle forms; deadlock detected; the whole migration rolls back. This was reproduced on the production database.

There’s an easy assumption to make: “nobody’s writing while the migration runs anyway.” It doesn’t hold: compose runs migrate to completion before it touches the api container, so during the migration the old api is still serving and increment_view keeps writing.

So one LOCK TABLE work IN ACCESS EXCLUSIVE MODE at the top grabs the strongest lock and never upgrades again — a cycle has no way to form.

Second judgment call: this is where I got it wrong

The first time this went to prd, lock_timeout was followed by “fail fast if the lock can’t be had.” The reasoning at the time: migrate exits non-zero, the api isn’t rebuilt, the old api keeps serving — so failing is the safe direction.

That judgment was wrong.

docker compose up -d does not preserve the old container just because migrate exited non-zero. It rebuilds api/web as usual; the new container just gets stuck at Created and won’t come up — depends_on: migrate: service_completed_successfully isn’t satisfied.

Net result: the migration didn’t take effect and the site went down outright. And the prd work table always has increment_view writing to it, so there was never any guarantee of grabbing ACCESS EXCLUSIVE within 15 seconds. An ordinary deploy took the site out.

The right approach is short timeout + repeated retries: wait only 2 seconds each round, let the queued write transactions finish, then try again. Online writes are all short transactions, so within a few rounds a window is bound to open. Only if 60 seconds pass without getting the lock do we RAISE — that means a long transaction is sitting on work, which calls for human intervention rather than forcing it.

FOR i IN 1..30 LOOP
    BEGIN
        PERFORM set_config('lock_timeout', '2s', true);
        LOCK TABLE work IN ACCESS EXCLUSIVE MODE;
        EXIT;
    EXCEPTION WHEN lock_not_available THEN
        IF i = 30 THEN
            RAISE EXCEPTION '60 秒内拿不到 work 的 ACCESS EXCLUSIVE 锁（有长事务压着？先查 pg_stat_activity 再重试）';
        END IF;
        PERFORM pg_sleep(1);
    END;
END LOOP;
PERFORM set_config('lock_timeout', '0', true);

A separate BEGIN/EXCEPTION per round creates an implicit savepoint, so a failed lock attempt rolls back only that round and doesn’t poison the whole migration transaction.

Error messages need to speak human

There’s one more gate before you create the unique index. If duplicates exist, it hands back a sentence you can act on directly, rather than a duplicate key value:

SELECT count(*), string_agg(lc, ', ' ORDER BY lc)
  INTO dup, sample
  FROM (SELECT lower(code) AS lc FROM work GROUP BY 1 HAVING count(*) > 1 LIMIT 20) t;
IF dup > 0 THEN
    RAISE EXCEPTION
      'work 存在 % 组大小写重复 code，须先人工合并（保留握有 source_link/向量的那行）再跑本迁移：%',
      dup, sample;
END IF;

“Keep the row that holds the source_link” has to be written in — reverse the merge direction and data is still lost, the unique index still gets built, and from then on that movie simply has no playback source.

Wrapping Up

EXECUTE 'DROP INDEX IF EXISTS idx_work_lower_code';
EXECUTE 'CREATE UNIQUE INDEX IF NOT EXISTS work_code_lower_key ON work (lower(code))';

Both go through EXECUTE: inside a DO block you could write the DDL directly, but the whole script uniformly uses EXECUTE to concatenate strings, tied together with the variables above so it reads as one thing from start to finish.

At the end of the migration file there’s also a ANALYZE work;, so the planner sees the new index as quickly as possible.

Looking back now, the file is 86 lines in total, and comments account for 52 of them. The longest passage is the record of that outage, reading “⚠️ This one was bought with production downtime”… Without it written down, the next person (or me three months later) will see “2 seconds × 30” and just think it’s verbose, then optimize it back to “fail fast.”