Re: ODBC Layer and the now() function

Has anyone seen any strange behaviour as regards the now() function when accessing a db via odbc layer. The behaviour I have seen (which I have mentioned in pervious post) is as follows:

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 shows 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?

For the first table, Table A, timestamp is obtained using timeofday. As mentioned the second table uses now(). There is a possibility that these two times will differ slightly. However, I do not understand why the time of entry into the second table could be earlier than the first table!? i.e.

Moserver receives the event - timestamps it as 't1'
-- time lapse before moserver computes the transaction and gives it to odbc.
Txn_begin- now() gets frozen to 't2'
Insert - now() should put it as 't2'
Txn_end()- done.

So firstly t2 should always be > t1 and the difference could be a few seconds but I found sometimes t2 < t1!..

Hi,

I hope the following link will help you.

http://www.postgresql.org/docs/8.0/static/plpgsql-expressions.html

On 12/5/05, Byrne Kevin-kbyrne01 <kbyrne01(at)motorola(dot)com> wrote:
>
> Has anyone seen any strange behaviour as regards the now() function when
> accessing a db via odbc layer. The behaviour I have seen (which I have
> mentioned in pervious post) is as follows:
>
> 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 shows 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?
>
> For the first table, Table A, timestamp is obtained using timeofday. As
> mentioned the second table uses now(). There is a possibility that these two
> times will differ slightly. However, I do not understand why the time of
> entry into the second table could be earlier than the first table!? i.e.
>
> Moserver receives the event - timestamps it as 't1'
> -- time lapse before moserver computes the transaction and gives it to
> odbc.
> Txn_begin- now() gets frozen to 't2'
> Insert - now() should put it as 't2'
> Txn_end()- done.
>
> So firstly t2 should always be > t1 and the difference could be a few
> seconds but I found sometimes t2 < t1!..
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Byrne Kevin-kbyrne01 <kbyrne01(at)motorola(dot)com> writes:
> Moserver receives the event - timestamps it as 't1'
> -- time lapse before moserver computes the transaction and gives it to odbc.
> Txn_begin- now() gets frozen to 't2'
> Insert - now() should put it as 't2'
> Txn_end()- done.

> So firstly t2 should always be > t1 and the difference could be a few seconds but I found sometimes t2 < t1!..

I'd wonder about clock skew between the machine that's running
"moserver" and the machine running Postgres.

Another possibility is that the "transaction begin" doesn't happen when
you think it does. Sometimes driver-level code like ODBC will issue
BEGIN behind your back ...

regards, tom lane