Hi Tom,
Thanks for the detailed consideration.
I can confirm that there is only one connection/transaction accessing the job record on the given database.
The flow is simply:
initiate test
create job
update job (using provided query)
compare timestamps
No other logic has access to the "job" record that is created in the isolated test (on the given database) other than an AFTER ROW trigger that simply does a NOTIFY (no modification of data).
The timestamp was generated by determining "now" in C++ code and provided as an ISO8601 string in the query.
When I say "on the given database" that is because the same logic is executed concurrently on two databases hosted by the same PostgreSQL server. Two separate connections. Two independent threads. Each thread using its own connection for both operations (create then update). Each thread using the same query.
So, the same update query (hence time) gets applied to two databases on the same server in parallel. In the real (production) world these will be separate servers.