Thread: BUG #4789: ERROR 22008 on timestamp import
The following bug has been logged online: Bug reference: 4789 Logged by: Robert Kruuus Email address: robert.kruus@gov.sk.ca PostgreSQL version: 8.3.7 Operating system: [FREEBSD] amd64-portbld-freebsd7.1, compiled by GCC cc (GCC) 4.2.1 Description: ERROR 22008 on timestamp import Details: Copying from a csv file (using both COPY as superuser and the psql \copy command) fails with DATETIME FIELD OVERFLOW for values with x.9999999Z (seven or more nines after the decimal) '1999-08-06 05:34:10.999999643Z' and '1999-08-06 00:12:57.999999900Z' will both fail where for example '1999-01-12 21:08:33.991259510Z' work correctly. I am not concerned about the rounding inherent in the datatype. More generally, even SELECT '1999-08-06 00:12:57.9999999Z'::timestamp with time zone; will throw the same error, so it seems to be something in the way Postgres rounds/truncates the field.
"Robert Kruuus" <robert.kruus@gov.sk.ca> writes:
> More generally, even
> SELECT '1999-08-06 00:12:57.9999999Z'::timestamp with time zone; will throw
> the same error, so it seems to be something in the way Postgres
> rounds/truncates the field.
Hmph. Is your installation built with --enable-integer-datetimes?
(Try "show integer_datetimes;" if you're not sure.)
On my devel machine, I get this in 8.3
regression=# select '1999-08-06 00:12:57.999999900Z'::timestamptz;
timestamptz
------------------------
1999-08-05 20:12:58-04
(1 row)
and this in 8.4
regression=# select '1999-08-06 00:12:57.999999900Z'::timestamptz;
ERROR: date/time field value out of range: "1999-08-06 00:12:57.999999900Z"
LINE 1: select '1999-08-06 00:12:57.999999900Z'::timestamptz;
^
but I bet it's the change in the default integer_datetimes setting
that is the relevant difference.
regards, tom lane
>>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> but I bet it's the change in the default integer_datetimes setting
> that is the relevant difference.
Confirmed.
cc=> select '1999-08-06 00:12:57.999999900Z'::timestamptz;
ERROR: date/time field value out of range: "1999-08-06
00:12:57.999999900Z"
cc=> select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20070115 (SUSE Linux)
(1 row)
cc=> show integer_datetimes ;
integer_datetimes
-------------------
on
(1 row)
-Kevin
"Kruus, Robert ENV" <Robert.Kruus@gov.sk.ca> writes:
>> Hmph. Is your installation built with --enable-integer-datetimes?
> Yes it is 'on'.
On further probing, I can make it happen with float datetimes too,
if I throw enough fractional nines in there:
regression=# select '1999-08-06 00:12:57.999999999999999999999999999900'::timestamptz;
ERROR: date/time field value out of range: "1999-08-06 00:12:57.999999999999999999999999999900"
The problem seems to be here:
/* do a sanity check */
#ifdef HAVE_INT64_TIMESTAMP
if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
tm->tm_sec < 0 || tm->tm_sec > 60 || *fsec < INT64CONST(0) ||
*fsec >= USECS_PER_SEC)
return DTERR_FIELD_OVERFLOW;
#else
if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
tm->tm_sec < 0 || tm->tm_sec > 60 || *fsec < 0 || *fsec >= 1)
return DTERR_FIELD_OVERFLOW;
#endif
With enough nines, the fsec value is going to round up to 1.0 (float
case) or USECS_PER_SEC (int case). So I think that this check ought
to allow, not exclude, the boundary value. And then we need to be
sure the subsequent code adds the values together properly, but that
probably happens okay already.
regards, tom lane
I wrote: > With enough nines, the fsec value is going to round up to 1.0 (float > case) or USECS_PER_SEC (int case). So I think that this check ought > to allow, not exclude, the boundary value. Patch applied here: http://archives.postgresql.org/message-id/20090501192913.C39EC75407B@cvs.postgresql.org regards, tom lane