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?
>=20
> 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=20
$ 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 =3D Fri Dec 13 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
PST  Sat Dec 14 20:45:52 1901 UTC =3D Sat Dec 14 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
PST  Mon Jan 18 03:14:07 2038 UTC =3D Mon Jan 18 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
PST  Tue Jan 19 03:14:07 2038 UTC =3D Tue Jan 19 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
PSD  Fri Dec 13 20:45:52 1901 UTC =3D Fri Dec 13 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
PSD  Sat Dec 14 20:45:52 1901 UTC =3D Sat Dec 14 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
PSD  Mon Jan 18 03:14:07 2038 UTC =3D Mon Jan 18 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
PSD  Tue Jan 19 03:14:07 2038 UTC =3D Tue Jan 19 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
GMT  Fri Dec 13 20:45:52 1901 UTC =3D Fri Dec 13 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
GMT  Sat Dec 14 20:45:52 1901 UTC =3D Sat Dec 14 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
GMT  Mon Jan 18 03:14:07 2038 UTC =3D Mon Jan 18 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
GMT  Tue Jan 19 03:14:07 2038 UTC =3D Tue Jan 19 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
CST  Fri Dec 13 20:45:52 1901 UTC =3D Fri Dec 13 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
CST  Sat Dec 14 20:45:52 1901 UTC =3D Sat Dec 14 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
CST  Mon Jan 18 03:14:07 2038 UTC =3D Mon Jan 18 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
CST  Tue Jan 19 03:14:07 2038 UTC =3D Tue Jan 19 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0

> 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=20
$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=3D0xbfbff670, ftype=3D0xbfbff60c, nf=3D=
2,=20
    dtype=3D0xbfbff5c4, tm=3D0xbfbff6d4, fsec=3D0xbfbff5c8, tzp=3D0xbfbff5d=
0)
    at datetime.c:892
warning: Source file is more recent than executable.

892     {
(gdb) c
Continuing.

Breakpoint 2, DetermineLocalTimeZone (tm=3D0xbfbff6d4) 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_mda=
y))
(gdb) print *tm
$1 =3D {tm_sec =3D 0, tm_min =3D 0, tm_hour =3D 2, tm_mday =3D 7, tm_mon =
=3D 4,=20
  tm_year =3D 2002, tm_wday =3D -1077938292, tm_yday =3D 138716656, tm_isds=
t =3D -1,=20
  tm_gmtoff =3D -1077938388, tm_zone =3D 0xbfbff72c "\214=F7=BF=BF"}
[snip]
1515            return tz;
(gdb) print tz
$2 =3D 1077938388
(gdb) print *tm
$3 =3D {tm_sec =3D 0, tm_min =3D 0, tm_hour =3D 2, tm_mday =3D 7, tm_mon =
=3D 4,=20
  tm_year =3D 2002, tm_wday =3D -1077938292, tm_yday =3D 138716656, tm_isds=
t =3D 0,=20
  tm_gmtoff =3D -1077938388, tm_zone =3D 0xbfbff72c "\214=F7=BF=BF"}
(gdb) n
DecodeDateTime (field=3D0xbfbff670, ftype=3D0xbfbff60c, nf=3D2, dtype=3D0xb=
fbff5c4,=20
    tm=3D0xbfbff6d4, fsec=3D0xbfbff5c8, tzp=3D0xbfbff5d0) at datetime.c:1448
1448            return 0;
(gdb) print *tm
$4 =3D {tm_sec =3D 0, tm_min =3D 0, tm_hour =3D 2, tm_mday =3D 7, tm_mon =
=3D 4,=20
  tm_year =3D 2002, tm_wday =3D -1077938292, tm_yday =3D 138716656, tm_isds=
t =3D 0,=20
  tm_gmtoff =3D -1077938388, tm_zone =3D 0xbfbff72c "\214=F7=BF=BF"}


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=3D1150, arg1=3D139024360, arg2=3D0, arg3=3D429=
4967295)
    at fmgr.c:1193
[snip]
1197            return result;
(gdb) n
0x80a40e3 in stringTypeDatum (tp=3D0x847ee00,=20
    string=3D0x84957e8 "2002-4-7 2:0:0.0", atttypmod=3D-1) at parse_type.c:=
181
181             return OidFunctionCall3(op,
(gdb) n
coerce_type (pstate=3D0x8495288, node=3D0x8495430, inputTypeId=3D705,=20
    targetTypeId=3D1184, atttypmod=3D-1) at parse_coerce.c:83

83                              pfree(val);
(gdb) print *pstate
$9 =3D {parentParseState =3D 0x0, p_rtable =3D 0x8495708, p_joinlist =3D 0x=
0,=20
  p_namespace =3D 0x0, p_last_resno =3D 2, p_forUpdate =3D 0x0,=20
  p_hasAggs =3D 0 '\000', p_hasSubLinks =3D 0 '\000', p_is_insert =3D 1 '\0=
01',=20
  p_is_update =3D 0 '\000', p_target_relation =3D 0x847fba0,=20
  p_target_rangetblentry =3D 0x84953a0}
[snip]
backend> select * from tt;
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2036-06-02 22:19:48-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D 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 =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
backend> insert into tt values ('2002-4-9 2:0:0.0');
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
backend> select * from tt;
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2036-06-02 22:19:48-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2002-04-08 02:00:00-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2002-04-09 02:00:00-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D 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 =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
backend> insert into tt values ('2002-4-7 3:0:0.0');
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
backend> select * from tt;
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2036-06-02 22:19:48-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2036-06-02 22:49:48-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2002-04-07 03:00:00-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----

Ideas where to look?  -sc

--=20
Sean Chittenden