Hi, While working on something I come across this issue. Consider following test:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
(1 row)
postgres=# create table test ( a timestamptz);
CREATE TABLE
-- Date with year 1000
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
INSERT 0 1
-- Now try with year 10000 it will return error
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
ERROR: invalid input syntax for type timestamp with time zone: "Sat Mar 11
23:58:48 10000 IST"
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
here error coming from timestamptz_in() -> datefields_to_timestamp() ->
DecodeDateTime() stack.
Looking more at the DecodeDateTime() function, here error coming while
trying
to Decode year field which is 10000 in the our test. For year field ftype is
DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following
condition:
else if (flen > 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
}
because flen in out case flen is 5 (10000).
As per the comment above DecodeNumberField(), it interpret numeric string
as a
concatenated date or time field. So ideally we should be into
DecodeNumberField
function only with (fmask & DTK_DATE_M) == 0 or (fmask & DTK_TIME_M) == 0,
right ??
So, I tried the same and after that test working fine.
Another fix could be to modify DecodeNumberField() to only check for the
date and time when (fmask & DTK_DATE_M) == 0 and (fmask & DTK_TIME_M) == 0.
And if DecodeNumberField() returns error then call DecodeNumber() to check
the year possibility. But I didn't
Results after fix:
postgres=# select * from test;
a
------------------------------
1000-03-12 03:52:16+05:53:28
10000-03-12 03:28:48+05:30
(2 rows)
PFA patch and share your input/suggestions.
(With patch make check running fine without additional failures)
Regards,
Rushabh Lathia
www.EnterpriseDB.com
timestamptz_fix.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
