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) comwrites:
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 DBC will issue
BEGIN behind your back

regards, tom lane

(end of broadcast)
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi,

I hope the following link will help you.

12/5/05, Byrne Kevin-kbyrne01 <kbyrne01 (AT) motorola (DOT) comwrote:

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