I'm still getting failure, but it gets weirder:

regression=# drop table timetz_tbl;
DROP TABLE
regression=# CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone);

INSERT INTO TIMETZ_TBL VALUES ('00:01 PDT');
CREATE TABLE
regression=#
regression=# INSERT INTO TIMETZ_TBL VALUES ('00:01 PDT');
INSERT INTO TIMETZ_TBL VALUES ('01:00 PDT');
INSERT INTO TIMETZ_TBL VALUES ('02:03 PDT');
INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
INSERT 154307 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('01:00 PDT');
INSERT INTO TIMETZ_TBL VALUES ('02:03 PDT');
INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
INSERT 154308 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('02:03 PDT');
INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
INSERT INTO TIMETZ_TBL VALUES ('12:00 PDT');
INSERT INTO TIMETZ_TBL VALUES ('12:01 PDT');
INSERT 154309 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
INSERT INTO TIMETZ_TBL VALUES ('12:00 PDT');
INSERT INTO TIMETZ_TBL VALUES ('12:01 PDT');
INSERT INTO TIMETZ_TBL VALUES ('23:59 PDT');
INSERT INTO TIMETZ_TBL VALUES ('11:59:59.99 PM PDT');
INSERT 154310 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
INSERT 154311 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
INSERT 154312 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('12:00 PDT');
INSERT 154313 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('12:01 PDT');
INSERT 154314 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('23:59 PDT');
INSERT 154315 1
regression=# INSERT INTO TIMETZ_TBL VALUES ('11:59:59.99 PM PDT');
INSERT 154316 1
regression=# SELECT f1 AS "Time TZ" FROM TIMETZ_TBL;
    Time TZ
----------------
 00:01:00-07
 01:00:00-07
 02:03:00-07
 07:07:00-08
 08:08:00-04
 11:59:00-07
 12:00:00-07
 12:01:00-07
 23:59:00-07
 23:59:59.99-07
(10 rows)

-- Now run EXACTLY the same query again...

regression=# SELECT f1 AS "Time TZ" FROM TIMETZ_TBL;
    Time TZ
----------------
 00:00:00-07
 00:00:00-07
 00:00:00-07
 00:00:00-08
 00:00:00-04
 00:00:00-07
 00:00:00-07
 00:00:00-07
 00:00:00-07
 00:00:59.99-07

What the heck!!?!?!?!  The SELECT statement has somehow edited the data in
the table or something???  From this point forward, the table is all
stuffed...

Chris


Thu, 13 Feb 2003 11:08:20 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
horology and time failures on freebsd/alpha
Oops - just to clarify I accidentally copied too many INSERTs into the
email.  The result of the first SELECT is correct compared to the INSERTs
that I did.  I copied the INSERTs and CREATE TABLE as-is from timetz.sql

Chris

> -----Original Message-----
> From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
> Sent: Thursday, 13 February 2003 11:08 AM
> To: Hackers
> Cc: Tom Lane
> Subject: horology and time failures on freebsd/alpha
>
>
> I'm still getting failure, but it gets weirder:
>
> regression=# drop table timetz_tbl;
> DROP TABLE
> regression=# CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone);
>
> INSERT INTO TIMETZ_TBL VALUES ('00:01 PDT');
> CREATE TABLE
> regression=#
> regression=# INSERT INTO TIMETZ_TBL VALUES ('00:01 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('01:00 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('02:03 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
> INSERT 154307 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('01:00 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('02:03 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
> INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
> INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
> INSERT 154308 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('02:03 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
> INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
> INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('12:00 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('12:01 PDT');
> INSERT 154309 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
> INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
> INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('12:00 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('12:01 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('23:59 PDT');
> INSERT INTO TIMETZ_TBL VALUES ('11:59:59.99 PM PDT');
> INSERT 154310 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
> INSERT 154311 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
> INSERT 154312 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('12:00 PDT');
> INSERT 154313 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('12:01 PDT');
> INSERT 154314 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('23:59 PDT');
> INSERT 154315 1
> regression=# INSERT INTO TIMETZ_TBL VALUES ('11:59:59.99 PM PDT');
> INSERT 154316 1
> regression=# SELECT f1 AS "Time TZ" FROM TIMETZ_TBL;
>     Time TZ
> ----------------
>  00:01:00-07
>  01:00:00-07
>  02:03:00-07
>  07:07:00-08
>  08:08:00-04
>  11:59:00-07
>  12:00:00-07
>  12:01:00-07
>  23:59:00-07
>  23:59:59.99-07
> (10 rows)
>
> -- Now run EXACTLY the same query again...
>
> regression=# SELECT f1 AS "Time TZ" FROM TIMETZ_TBL;
>     Time TZ
> ----------------
>  00:00:00-07
>  00:00:00-07
>  00:00:00-07
>  00:00:00-08
>  00:00:00-04
>  00:00:00-07
>  00:00:00-07
>  00:00:00-07
>  00:00:00-07
>  00:00:59.99-07
>
> What the heck!!?!?!?!  The SELECT statement has somehow edited
> the data in the table or something???  From this point forward,
> the table is all stuffed...
>
> Chris
>


Thu, 13 Feb 2003 11:20:19 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: horology and time failures on freebsd/alpha
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> What the heck!!?!?!?!  The SELECT statement has somehow edited
>> the data in the table or something???  From this point forward,
>> the table is all stuffed...

Hm, time to dig out the debugger and figure out where the breakage
is.  Do you want to do it, or lend an account to let someone else
dig into it?

			regards, tom lane

Wed, 12 Feb 2003 22:27:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: horology and time failures on freebsd/alpha 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I'm still getting failure, but it gets weirder:

Fixed --- turns out the bug is that timetz_out was scribbling on its
input (thereby changing the table) in the --enable-integer-datetimes
path.

I back-patched the change into 7.3, although I believe the bug cannot
occur in just this form in 7.3.

			regards, tom lane

Thu, 13 Feb 2003 12:21:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: horology and time failures on freebsd/alpha 