Friday Fixes: A Meta Fix of Last Friday’s Fix, Thanks to PostgreSQL

This week, Friday Fixes has acquired its own Friday Fix. I fixed my fitness tracker, wrote about fixing it, and then discovered the fix needed a fix. PostgreSQL would like a word.

My fitness tracker had just passed 59 tests. I had reviewed the fixes, merged the changes, and written about the audit.

Then I tried using it.

On October 14, Tonal sync returned an internal server error. Renpho sync failed too. Two integrations, both broken, immediately after a round of work that was supposed to make the app more reliable.

The previous post ended with a caveat: we still needed to exercise the database paths against real PostgreSQL. Apparently, production had volunteered.

Two broken syncs shared one query

I brought both failures back to the coding session. Tonal gave me an error message. Renpho gave me a screenshot. Production logs supplied the useful part.

Both syncs failed inside the same database helper. Before writing imported data, the helper acquired a transaction-level advisory lock. That lock keeps competing sync writes from stepping on each other.

The flow looked like this:

Tonal sync ----+
              |
              +--> Shared write helper --> PostgreSQL lock --> Import data
              |
Renpho sync --+

Neither integration reached the import. PostgreSQL rejected the lock call first.

That narrowed the investigation considerably. I did not have two separate authentication failures or two upstream services changing their APIs. I had two callers hitting the same broken query.

A number arrived wearing the wrong size

PostgreSQL reported that this function did not exist:

pg_advisory_xact_lock(integer, bigint)

The function exists, but that particular combination of argument types does not. PostgreSQL provides two relevant signatures: one takes a single 64-bit integer; the other takes two 32-bit integers.

Our helper used the two-key version. One key identified the lock namespace. The other identified the sync scope. Both needed to arrive as PostgreSQL integers.

Prisma’s raw query behavior was the catch. It sent the bound JavaScript number as a PostgreSQL bigint. The query therefore asked for the nonexistent mixed-type signature.

The fix explicitly cast the bound scope key to the integer type the function expected. The lock stayed in place. Its keys stayed the same. The database schema stayed the same.

Removing the lock might have made the immediate error disappear. It would also have removed the protection we had added for concurrent writes. I wanted working syncs with the lock intact.

The tests had never asked PostgreSQL

The existing tests mocked database transactions. They could check the application’s control flow without sending this query through Prisma to PostgreSQL.

That explains the green suite. A mock does not resolve PostgreSQL function overloads. It cannot reject a signature it never evaluates.

The repair needed a test that crossed that boundary. We added four integration tests using Prisma and a real PostgreSQL database. Together, they covered:

• Reproducing the original failure without the cast.
• Acquiring the corrected locks for both sync scopes.
• Keeping the separate scopes independent.
• Releasing locks after a commit or a callback failure.

The local sandbox could not start PostgreSQL because of shared-memory restrictions. Rather than replace the database with another mock, we ran the checks against an isolated PostgreSQL service in GitHub Actions.

That gave us a useful distinction: the old query failed against the real database, and the corrected query passed. The test could catch the bug we had actually shipped.

Green checks were one step in the repair

The fix in PR 38 passed all 63 tests. Lint completed with two existing warnings, and the production build passed. The preview deployment passed too.

Those checks answered different questions:

I approved the merge. The production deployment succeeded. Then I refreshed the app and retried the syncs.

My reply in the session was considerably shorter than this post:

Perfect. Fixed. Thanks.

That confirmation mattered. A successful deployment tells me the code reached production. Using the app tells me whether the thing I came to fix works.

The next database helper gets a database test

The earlier audit improved the app, but its passing tests left a specific gap. We had checked the code around a database operation without checking whether the database could execute it.

I do not need every test to boot PostgreSQL. I do want a real database involved when correctness depends on SQL types, function signatures, or lock behavior.

This was a small fix with a useful consequence. The suite now contains a reproduction of the failure, so the next change to that helper has something more concrete to argue with than my confidence.

And I can get back to tracking workouts instead of debugging the part that tracks them. Which is, regrettably, not a workout.

By the Numbers

• 2 broken syncs: Tonal and Renpho.
• 1 shared database query behind both failures.
• 59 existing tests that passed before PostgreSQL got a vote.
• 4 new tests with an actual database in the room.
• 63 passing tests after the fix.
• 0 schema migrations required.
• 1 very satisfying “Fixed. Thanks.”