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
Show quoted text
On 12/5/05, Byrne Kevin-kbyrne01 <kbyrne01@motorola.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?
Hi,
the following link might help you.
http://www.postgresql.org/docs/8.0/static/plpgsql-expressions.html
On 12/5/05, Byrne Kevin-kbyrne01 <kbyrne01@motorola.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?
---------------------------------
Yahoo! Personals
Single? There's someone we'd like you to meet.
Lots of someones, actually. Yahoo! Personals
Import Notes
Resolved by subject fallback
Byrne Kevin-kbyrne01 <kbyrne01@motorola.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