Re: Date precision problem - Mailing list pgsql-general

Thomas Lockhart wrote:
>
> Could you be more specific about the problem? I first interpreted this
> as a problem with now(), but now that I read this again you are talking
> about updating fields so that is not the actual test case is it?


Hi,

These are all the steps that show the problem.

----
 1
----
In PostgreSQL 7.1.3 create a table and a trigger to update it.

create table "test" (
 id          integer,
 name        character varying(20),
 last_update timestamp with time zone
);

CREATE FUNCTION LASTUPDATE_TEST ()
             RETURNS OPAQUE AS '
             BEGIN
             new."last_update" = ''now'';
             RETURN new;
             END;
             ' LANGUAGE 'plpgsql';
CREATE TRIGGER last_update
BEFORE UPDATE OR INSERT ON "test"
FOR EACH ROW EXECUTE PROCEDURE LASTUPDATE_TEST();

----
 2
----
Insert 3 rows in the table


 select * from test;
 id |  name  |      last_update
----+--------+------------------------
  1 | name 1 | 2002-04-17 16:56:38+02
  2 | name 2 | 2002-04-17 16:56:54+02
  3 | name 3 | 2002-04-17 16:57:00+02
(3 rows)


----
 3
----
From ACCESS update one of the rows in the table. Here are the logs for this
update.

DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT "id","name","last_update"  FROM "test"  WHERE "id" = 1
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: BEGIN
DEBUG:  ProcessUtility: BEGIN
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: UPDATE "test" SET "name"='name 1 update 1'  WHERE "id" = 1 AND
"name" = 'name 1' AND "last_update" = '2002-04-17 16:56:38'
DEBUG:  ProcessQuery
DEBUG:  query: SELECT  'now'
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: COMMIT
DEBUG:  ProcessUtility: COMMIT
DEBUG:  CommitTransactionCommand


----
 4
----
From ACCESS update again the same row in the table.


DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT "id","name","last_update"  FROM "test"  WHERE "id" = 1
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: BEGIN
DEBUG:  ProcessUtility: BEGIN
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: UPDATE "test" SET "name"='name 1 update 2'  WHERE "id" = 1 AND
"name" = 'name 1 update 1' AND "last_update" = '2002-04-17 16:59:02'
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: COMMIT
DEBUG:  ProcessUtility: COMMIT
DEBUG:  CommitTransactionCommand


----
 5
----
The result after both updates


select * from test;
 id |      name       |      last_update
----+-----------------+------------------------
  2 | name 2          | 2002-04-17 16:56:54+02
  3 | name 3          | 2002-04-17 16:57:00+02
  1 | name 1 update 2 | 2002-04-17 16:59:58+02
(3 rows)


----
 6
----
Import the table to PostgreSQL 7.2 and the result is correct:

select * from test;
 id |      name       |      last_update
----+-----------------+------------------------
  2 | name 2          | 2002-04-17 16:56:54+02
  3 | name 3          | 2002-04-17 16:57:00+02
  1 | name 1 update 2 | 2002-04-17 16:59:58+02
(3 rows)


----
 7
----
Then make an update over the same row in the table imported in PostgresSQL 7.2


DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT "id","name","last_update"  FROM "test"  WHERE "id" = 1
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: BEGIN
DEBUG:  ProcessUtility: BEGIN
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: UPDATE "test" SET "name"='name 1 update 3'  WHERE "id" = 1 AND
"name" = 'name 1 update 2' AND "last_update" = '2002-04-17 16:59:58'
DEBUG:  ProcessQuery
DEBUG:  query: SELECT  'now'
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: COMMIT
DEBUG:  ProcessUtility: COMMIT
DEBUG:  CommitTransactionCommand


----
 8
----
The row is updated succesfully.


 select * from test;
 id |      name       |          last_update
----+-----------------+-------------------------------
  2 | name 2          | 2002-04-17 16:56:54+02
  3 | name 3          | 2002-04-17 16:57:00+02
  1 | name 1 update 3 | 2002-04-17 16:40:44.548177+02
(3 rows)


----
 9
----
Try to update the same row from ACCESS again. And a rollback is made by Postgres


DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT "test"."id" FROM "test"
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT "id","name","last_update"  FROM "test"  WHERE "id" = 2 OR
"id" = 3 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1
OR "id" = 1 OR "id" = 1
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: SELECT "id","name","last_update"  FROM "test"  WHERE "id" = 1
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: BEGIN
DEBUG:  ProcessUtility: BEGIN
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: UPDATE "test" SET "name"='name 1 update 4'  WHERE "id" = 1 AND
"name" = 'name 1 update 3' AND "last_update" = '2002-04-17 16:40:44.548'
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: ROLLBACK
DEBUG:  ProcessUtility: ROLLBACK
DEBUG:  CommitTransactionCommand

--------------------------------


This is all the sequence to obtain the error. ACCESS includes in the where
clause "last_update" = '2002-04-17 16:40:44.548' and postgres has '2002-04-17
16:40:44.548177+02'  These two dates are different and the row is not updated
because there is not matching row.

The field last_update receives the value of the function now (by the trigger).
Then if we execute
select now();  in Postgres 7.1.3 we obtain
          now
------------------------
 2002-04-17 18:01:58+02

But select now(); in Postgres 7.2 returns
              now
-------------------------------
 2002-04-17 17:06:11.937501+02

The date representation is different.


I hope the explanation is clear now.

bye & thanks.