Re: Bug #630: date/time storage problem: timestamp parsed

> > PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3 This
> > isn't happy making. What OS are you running? Seems like a lower
> > level problem. Do you know if it's a system call making the
> > formatting call?
>
> PostgreSQL uses system calls to get the current time zone if it is
> not specified in the input string.

I'm inclined to agree after having stepped through things.

> I'm running a fairly new Linux (Mandrake distro), which has the zinc
> package as part of glibc-2.2.4

The what package? <:~)

> Do you have another way to verify your time zone setup? Do you have
> the "zdump" command to look at your time zone info?

It appears to be correct:

$ date
Tue Apr 9 14:40:51 PDT 2002
$ zdump
$ zdump PST PSD GMT CST
PST Tue Apr 9 21:40:15 2002 GMT
PSD Tue Apr 9 21:40:15 2002 GMT
GMT Tue Apr 9 21:40:15 2002 GMT
CST Tue Apr 9 21:40:15 2002 GMT
$ zdump -v PST PSD GMT CST
PST Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
PST Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
PST Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
PST Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
PSD Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
PSD Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
PSD Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
PSD Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
GMT Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
GMT Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
GMT Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
GMT Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
CST Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
CST Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
CST Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
CST Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0

> If you want to step through your PostgreSQL code, I could give you
> some suggestions on what to look for:
[snip]

(gdb) b DecodeDateTime
Breakpoint 1 at 0x811568d: file datetime.c, line 892.
(gdb) b DetermineLocalTimeZone
Breakpoint 2 at 0x81161a9: file datetime.c, line 1463.
(gdb) run foo
Starting program: /opt/ports/databases/postgresql7/work/postgresql-7.2/src/backend/postgres foo
DEBUG: database system was shut down at 2002-04-09 14:42:06 PDT
DEBUG: checkpoint record is at 0/12B514
DEBUG: redo record is at 0/12B514; undo record is at 0/0; shutdown TRUE
DEBUG: next transaction id: 109; next oid: 32942
DEBUG: database system is ready

POSTGRES backend interactive interface
$Revision: 1.245 $ $Date: 2002/01/10 01:11:45 $

backend> create table tt ( tt timestamp );
backend> insert into tt values ('2002-4-7 2:0:0.0');

Breakpoint 1, DecodeDateTime (field=0xbfbff670, ftype=0xbfbff60c, nf=2,
dtype=0xbfbff5c4, tm=0xbfbff6d4, fsec=0xbfbff5c8, tzp=0xbfbff5d0)
at datetime.c:892
warning: Source file is more recent than executable.

892 {
(gdb) c
Continuing.

Breakpoint 2, DetermineLocalTimeZone (tm=0xbfbff6d4) at datetime.c:1463
1463 {
(gdb) s
1466 if (HasCTZSet)
(gdb) n
1468 else if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mday))
(gdb) print *tm
$1 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = -1,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}
[snip]
1515 return tz;
(gdb) print tz
$2 = 1077938388
(gdb) print *tm
$3 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = 0,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}
(gdb) n
DecodeDateTime (field=0xbfbff670, ftype=0xbfbff60c, nf=2, dtype=0xbfbff5c4,
tm=0xbfbff6d4, fsec=0xbfbff5c8, tzp=0xbfbff5d0) at datetime.c:1448
1448 return 0;
(gdb) print *tm
$4 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = 0,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}

It looks as though the data is getting parsed correctly. Could it be
that the data is getting written incorrectly?

[further down in the gdb session]
OidFunctionCall3 (functionId=1150, arg1=139024360, arg2=0, arg3=4294967295)
at fmgr.c:1193
[snip]
1197 return result;
(gdb) n
0x80a40e3 in stringTypeDatum (tp=0x847ee00,
string=0x84957e8 "2002-4-7 2:0:0.0", atttypmod=-1) at parse_type.c:181
181 return OidFunctionCall3(op,
(gdb) n
coerce_type (pstate=0x8495288, node=0x8495430, inputTypeId=705,
targetTypeId=1184, atttypmod=-1) at parse_coerce.c:83

83 pfree(val);
(gdb) print *pstate
$9 = {parentParseState = 0x0, p_rtable = 0x8495708, p_joinlist = 0x0,
p_namespace = 0x0, p_last_resno = 2, p_forUpdate = 0x0,
p_hasAggs = 0 '\000', p_hasSubLinks = 0 '\000', p_is_insert = 1 '\001',
p_is_update = 0 '\000', p_target_relation = 0x847fba0,
p_target_rangetblentry = 0x84953a0}
[snip]
backend> select * from tt;
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2036-06-02 22:19:48-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----

What seems to be particularly interesting is the following:

backend> insert into tt values ('2002-4-8 2:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
----
backend> insert into tt values ('2002-4-9 2:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
----
backend> select * from tt;
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2036-06-02 22:19:48-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2002-04-08 02:00:00-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2002-04-09 02:00:00-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----

It seems as if this problem only happens with dates that happen
_during_ the date switch.

backend> insert into tt values ('2002-4-7 2:30:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
----
backend> insert into tt values ('2002-4-7 3:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
----
backend> select * from tt;
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2036-06-02 22:19:48-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2036-06-02 22:49:48-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2002-04-07 03:00:00-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----

Ideas where to look? -sc

--
Sean Chittenden