Re: Anomalies with the now() function

On Thu, Nov 17, 2005 at 12:30:36PM -0000, Byrne Kevin-kbyrne01 wrote:
> I have a trigger set up on a db - when a row is added to a certain
> table (say Table A) in my db the trigger calls a function and then the
> function enters another line in a related table (say Table B). Here's
> the problem, the first addition to Table A may show the time of the
> addition as, for example 19:01:53. This is correct. The second addition,
> triggered by the first additon, shows a time of say 19:01:10! The
> addition of the row to Table B uses the now() function to determine the
> time the new row is added to the table. This should in theory match the
> time (to within a few milliseconds at least) the first row was added,
> since the trigger is immediate. However, I am seeing major time
> differences? How reliable is now() - has anyone seen anything similar ?

now() doesn't advance during transactions and all statements are
wrapped in a transaction whether you explicitly start one or not,
so if you used now() for both inserts then the timestamps should
be identical regardless of how much time passed between the outer
insert and the one in the trigger. If you want wall time then
use timeofday().

How are you setting the timestamp for the insert to Table A? If
you're getting the timestamp in client code or from timeofday()
then that could explain the discrepancy: Table A gets "now" in the
sense of wall time, while Table B gets "now" in the sense of whenever
the transaction froze its notion of the current time, which may
have been a while earlier.

If that explanation doesn't fit with what you're doing then please
post a self-contained example that exhibits the behavior you're
seeing.

--
Michael Fuhr