Re: Date precision problem

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.