Miss Dada  🏳️‍⚧️

Posgres triggers in transactions. I write a script that begins, modifies a row, which fires a the trigger, then selects the column modified by the trigger. It was modified. (In psql, autocommit=off.) Run exactly the same queries inside my app and the trigger doesn't fire. Why?

When running the exact same queries in the app, but outside a transaction, the trigger runs as expected. It's a "before update" trigger, which isn't deferrable even if we wanted to, so deferring isn't the problem. I'm so confused by this.

I figured it out. Update triggers don't fire for modifications to a row created in the current transaction. If you begin, insert a row, and modify that row, "before update" triggers won't fire. But if you insert a row, begin, and modify that row, the triggers will fire.

I am vexed.

I'm using this trigger to update the `updated_at` fields of tables whenever they change. A correct `updated_at` might theoretically be important, even inside a transaction where the row was created and then modified. So this seems like a big problem.

Admittedly, the probability of this mattering in production code is relatively small. My actual immediate problem is that all tests are run inside a transaction, and I want to write a test for this updated_at behavior, and I can't!