SQL traps · Chapter 16 of 16

Gaps in a sequence

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Time
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: a backfill that stops early, on purpose

    Write repro.sql. It runs in a fresh, empty database where backfill.sql has just made the tables posts and backfilled and the function backfill(batch). Add five posts, so the sequence gives them ids 1 to 5. Then begin a transaction, add four more posts and roll it back, like the failed import: ids 6 to 9 are used up for good. Then add three more posts, which get ids 10 to 12. Let the sequence hand out every id: never write one yourself. End with a SELECT that gives one value: what backfill(3) returns, the number of posts it processed.

    The check runs your file twice, each time in a fresh database: beside the original backfill.sql it must give 5 (it stops at the empty range 7 to 9), and beside a fixed backfill.sql it must give 8. So it has to really make the gap: a file that only says SELECT 5 fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    backfill.sql: the backfill reaches the last post

    Fix backfill(batch) in backfill.sql so it counts the words of every post into backfilled, exactly once, however the ids are spread out: small gaps, a gap far longer than a batch, or none at all. It works with any batch size, 1 or 1000 included, returns how many posts it processed, and with no posts at all returns 0 with no error. Each call starts from an empty backfilled.

    Keep the two tables as they are, and keep backfill's name, its argument, its integer result and how it counts words. The check makes its own posts with gaps of every size, and a fix that only steps over small gaps is still wrong.

  3. Task 3

    backfill.test.sql: a test that would have caught it

    Write a SQL test file, backfill.test.sql, that would have caught this bug before the backfill ran on production. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file three times, each in a fresh database where a version of backfill.sql has just made the tables: beside the original backfill.sql at least one test must fail; beside the fixed backfill.sql every test must pass and nothing may raise an error; beside a hotfix that only stops after two empty ranges in a row at least one test must fail.

    So make posts with a gap in their ids longer than three batches, for example ten ids burned by a rolled-back insert with a batch of 3, and posts after the gap. Then test that backfill processes every post, the ones after the gap too. Run it with node run.mjs backfill.test.sql.

What you will learn

  • repro.sql: a backfill that stops early, on purpose
  • backfill.sql: the backfill reaches the last post
  • backfill.test.sql: a test that would have caught it

More in Time

Build "Gaps in a sequence" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.