Stopping Time in PostgreSQL(blog.thefourthparty.com)
blog.thefourthparty.com
Stopping Time in PostgreSQL
http://blog.thefourthparty.com/stopping-time-in-postgresql/
3 comments
This behaviour is a defined by the SQL standard. PostgreSQL provides the non-standard statement_time() and clock_time() functions to get the time at the beginning of the execution of the current statement and the actual current timestamp, respectively. Even then, it's probably a good idea to use sequences rather than timestamps if strict ordering is required.
Indeed, and now() is often good enough, if not better, as it represents the conditions at the time that the logic was executed.
The author needs to examine carefully why a value is being updated so many times within a single transaction (first example provided), which by it's own nature is an atomic change (there's no reason to write the middle states to disk as it'll either commit or it won't).
Second the author provides a multiple clients (writers) example. In this case if the ordering is important a LOCK is likely required; however it is more likely that consistency is required (in this case likely the psql specific functions).
The author needs to examine carefully why a value is being updated so many times within a single transaction (first example provided), which by it's own nature is an atomic change (there's no reason to write the middle states to disk as it'll either commit or it won't).
Second the author provides a multiple clients (writers) example. In this case if the ordering is important a LOCK is likely required; however it is more likely that consistency is required (in this case likely the psql specific functions).
I was bitten by this once when I didn't know that. I had an application that rarely did inserts and had autocommit turned off. So one select freezed now() for few days until next insert. The result was me having few straight days logged into the timesheet over weekend.
This got me; often you want clock_timestamp() or timeofday().
I wrote about it, too - http://imperialwicket.com/postgresql-getting-now-in-function...
I wrote about it, too - http://imperialwicket.com/postgresql-getting-now-in-function...