Stopping Time in PostgreSQL

http://blog.thefourthparty.com/stopping-time-in-postgresql/

by luu • 11 years ago

25 4 11 years ago

blog.thefourthparty.com

DrJokepu• 11 years ago

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.

mjevans• 11 years ago

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).

xchg• 11 years ago

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.

imperialWicket• 11 years ago

This got me; often you want clock_timestamp() or timeofday().

I wrote about it, too - http://imperialwicket.com/postgresql-getting-now-in-function...

Stopping Time in PostgreSQL

Related Stories