Tarik Al Hadethi Download CV

A race my tests could not reproduce

Two people accepting the last seat on a team plan at the same instant could both succeed, and one of them would get a paid seat for free. Every sequential test passed. The problem was that my testing tool could not produce two things happening at once.

Clarus sells team plans by the seat. Accepting an invite checks the seat count, and if there is room, inserts a membership. Written down like that the bug is obvious: two accepts that both read the count before either writes will both believe there is room.

I had written the lock for exactly this. The question was whether it worked, and that turned out to be much harder to answer than to ask.

Why the tests were useless

Every test I could write ran the accepts one after another, and sequentially the code is correct. The SQL tooling I was using serialises statements, so two calls issued “at once” were still executed in order. I was not testing the lock. I was testing that a function called twice in a row behaves.

A test that cannot produce the condition is not evidence about the condition, however green it is.

Getting genuine concurrency

What finally worked was pg_cron. Each job runs in its own background session, which is real parallelism inside the database rather than an imitation of it from outside.

  • Job one took the org row with select ... for update and held it for 15 seconds.
  • Job two called the real accept_org_invite against that org.
  • Job three made the identical call against an org nobody was holding, as a control.

The numbers settled it:

contended    11,980 ms
uncontended       5-8 ms

The contended call returned about a millisecond after the holder committed. The lock is real, it is taken, and it is honoured. Without the control I would have had one slow number and no way to know whether it meant anything.

Two ways the test lied first

Both produced a confident, wrong answer before I noticed:

  • The caller was already in the team. The function returned at that gate in 3 ms and never reached the lock at all. It looked like a fast, passing call.
  • pg_cron fires jobs on offset ticks. My “concurrent” jobs kept missing each other entirely until the lock was held for 15 seconds of every 20.

The check beside the lock was not real

While proving the lock, I read the rest of the function properly, and found something worse than the race I was chasing.

An invite is only valid for the person it was sent to, and the function enforced that by comparing the invite’s email to the caller’s:

if invite_email <> v_email then
  raise exception 'not your invite';
end if;

In SQL, a comparison against NULL is not false. It is NULL, and the branch does not fire. So if v_email was ever NULL, the check passed silently. And it can be NULL: a deleted account whose access token is still inside its hour of life has a valid auth.uid() and no row in auth.users to read an email from.

Measured against the live function, that call passed this gate and every gate after it, and reached the insert. The only thing that stopped it was a foreign key, which failed as an unhandled 23503 rather than as a refusal. The invariant that stops an invite token being a bearer credential was being enforced one layer down, by accident.

A check that passes on NULL is not a check.

The fix is one line: reject a NULL email before comparing anything. The important part is the ordering, because a guard placed after the comparison it is meant to protect is decoration.

What I do differently now

Both invariants are asserted by a checker that runs on every change, along with a third: that the functions are revoked from Supabase’s anonymous role by name, because revoking them from public alone leaves them callable. Each has a negative control: the test removes the lock, moves it below the seat count, deletes the email check, revokes from public only, and confirms the checker catches all four. A test suite that has never been shown to fail is a suite of unverified claims.

And the wider one: if a bug needs two things to happen at once, the first job is not fixing it. The first job is building something that can make them happen at once. Until then you are guessing, and green tests will help you feel certain about the guess.

Written while building Clarus.  ·  More writing