Miss Dada  🏳️‍⚧️

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!

Apparently this was all wrong. My problem was that neither now() nor CURRENT_TIMESTAMP changes during a transaction. But there is clock_timestamp(), which does change. Thank you @sgrif, as usual!https://twitter.com/sgrif/status/1111368222861746176 …

FWIW I think using `CURRENT_TIMESTAMP` is what users would expect. Assuming you're testing the trigger in general, and not that it's created for specific tables, you can test by creating a new table in the test outside a transaction. https://github.com/diesel-rs/diesel/blob/50798e5dd52ae9d3c7c9a4bf7a5f1e5b9167aba3/diesel_tests/tests/connection.rs#L12-L75 …

(Our trigger and helper function https://github.com/diesel-rs/diesel/blob/50798e5dd52ae9d3c7c9a4bf7a5f1e5b9167aba3/diesel_cli/src/setup_sql/postgres/initial_setup/up.sql#L8-L36 …)