Обсуждение: Date precision problem
Hi,
I having some problem with the date fields migration from 7.1.3 version to 7.2
version...
In my current version PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC
2.96 I get this result
select now();
now
------------------------
2002-04-11 12:58:33+02
(1 row)
In the new version PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
SELECT NOW();
now
-------------------------------
2002-04-11 13:02:27.943119+02
(1 row)
The date is different in the last part:
2002-04-11 13:02:27.943119+02
^^^^^^^^^
Then when I am trying to update this field from ACCESS I have an error and the
entry can't be updated because the dates are not equals.
Someone can help me?
Thanks.
noy wrote:
>
> Hi,
>
> I having some problem with the date fields migration from 7.1.3 version to 7.2
> version...
>
> In my current version PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC
> 2.96 I get this result
>
> select now();
> now
> ------------------------
> 2002-04-11 12:58:33+02
> (1 row)
>
> In the new version PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
>
> SELECT NOW();
> now
> -------------------------------
> 2002-04-11 13:02:27.943119+02
> (1 row)
>
> The date is different in the last part:
>
> 2002-04-11 13:02:27.943119+02
> ^^^^^^^^^
> Then when I am trying to update this field from ACCESS I have an error and the
> entry can't be updated because the dates are not equals.
Is there no problem with 7.1.3 ?
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue wrote: > > noy wrote: > > > > Hi, > > > > I having some problem with the date fields migration from 7.1.3 version to 7.2 > > version... > > Is there no problem with 7.1.3 ? No, I have no problem in the version 7.1.3 with psqlodbc_07_01_0009 driver. Bye
noy wrote:
>
> Hiroshi Inoue wrote:
> >
> > noy wrote:
> > >
> > > Hi,
> > >
> > > I having some problem with the date fields migration from 7.1.3 version to 7.2
> > > version...
>
> >
> > Is there no problem with 7.1.3 ?
>
>
>
> No, I have no problem in the version 7.1.3 with psqlodbc_07_01_0009 driver.
Here I see no problem with 7.2 server though I see
the problem with 7.1.3 server.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
> I having some problem with the date fields migration from 7.1.3 version to 7.2
> version...
> In the new version PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
> The date is different in the last part:
> 2002-04-11 13:02:27.943119+02
> ^^^^^^^^^
> Then when I am trying to update this field from ACCESS I have an error and the
> entry can't be updated because the dates are not equals.
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?
What entry are you updating? What are you comparing to be equal? Is this
some funny business with Access generating updates from keys it is
holding internally (I recall something about it doing that)?
If for some reason you need less precision in your date/times, you can
now explicitly set the precision of a column:
thomas=# select timestamp without time zone 'now',
thomas=> timestamp(2) without time zone 'now',
thomas=> timestamp(0) without time zone 'now';
timestamp | timestamp |
timestamp
----------------------------+------------------------+---------------------
2002-04-17 06:34:33.032644 | 2002-04-17 06:34:33.03 | 2002-04-17
06:34:33
But afaik others have not seen a problem; could you have some funny
settings in Access which are causing your queries to fail? I haven't
used it myself, so can't help in more detail I'm afraid...
- Thomas
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.
...
> 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.
So how did Access decide that it had a three digit fractional seconds
field? afaik neither PostgreSQL nor its ODBC driver are truncating the
time.
If you need to constrain times to have a limited precision, you might
try defining things as (in this case) "timestamp(3)" rather than just
"timestamp". And perhaps others have some experience in tweaking Access
to give you, uh, access to the data you have.
- Thomas
On Wed, Apr 17, 2002 at 06:34:49PM +0200, noy wrote: > 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? > > 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 Ok, what's happening is that Access is trying to update the row and the only way to identify the row is by matching all the fields. Access is truncating the date, hence it can't update. Solution: define a primary key on the table and tell access to use that. Then it won't rely on properties of the date types to update successfully. You may be able to fix it on the postgres by telling it to use a date type that does not store nanoseconds. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Ignorance continues to thrive when intelligent people choose to do > nothing. Speaking out against censorship and ignorance is the imperative > of all intelligent people.
noy wrote:
>
> ----
> 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
Oh probably I see your point.
How about changing the line
new."last_update" = ''now'';
to
new."last_update" = ''now''::timestamp(0);
or creating the last_update field as timestamp(0)
from the first ?
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/