PostgreSQL: CURRENT_TIMESTAMP and CLOCK_TIMESTAMP Solutions: Windows vs. Linux?
So I have this interesting question about PostgreSQL timing functions.
Here’s the situation. We have a pre-production server (Linux) to house the applications we are developing. I’m also doing some work on the local copy of that database (Windows) in case the server is doing some more important work. I recently ran into an issue where I started seeing primary key violations on the log tables of my local database copy. I don’t think this is possible because I use CLOCK_TIMESTAMP (current system time) as the primary key. Also, I tested on a pre-production server and it worked fine. So I did some research. I eventually found that if I ran “SELECT CLOCK_TIMESTAMP()” on the server, it returned the time to microseconds. If I run it on my localhost, it only drops to milliseconds. Therefore, the problem arises when multiple updates occur before the timer reaches the next millisecond, something that is definitely possible in some of our processes.
So my question is this. Why is this happening and how can I fix it? Is this some obscure setting that I haven’t found yet? Or is Windows and Linux having a different timer resolution?
EDIT: The same thing happens with CURRENT_TIMESTAMP, NOW() and all the other built-in functions that return timestamps.
Reference Tom Lane: on this pg_hackers thread
I suppose what you’re really asking about is not the precision of the
datatype but the precision of now() readings. You’re out of luck —
Windows just doesn’t expose a call to get the wall clock time to better
than 1 msec.
Keep in mind that whatever the Linux machine is returning might be
largely fantasy in the low-order bits, too.
To resolve your issue, consider using the serial number as the primary key. (Suppose, of course, that you actually need the primary key of the log file first.) )