I've been puzzled by a couple of recent buildfarm failures:
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=dawn_bat&dt=2008-05-22%2006:00:01
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=vaquita&dt=2008-05-19%2020:00:03
Both fail the timestamp test like this:
*** ./expected/timestamp.out Mon May 19 21:01:31 2008
--- ./results/timestamp.out Mon May 19 21:34:23 2008
***************
*** 36,42 **** SELECT count(*) AS None FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'now'; none ------
! 0 (1 row) DELETE FROM TIMESTAMP_TBL;
--- 36,42 ---- SELECT count(*) AS None FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'now'; none ------
! 1 (1 row) DELETE FROM TIMESTAMP_TBL;
which seemed a bit baffling until I looked into the test source:
CREATE TABLE TIMESTAMP_TBL ( d1 timestamp(2) without time zone);
INSERT INTO TIMESTAMP_TBL VALUES ('now');
-- six more inserts and three selects
SELECT count(*) AS None FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'now';none
------ 0
(1 row)
IOW, this test is assuming that the value of now() must change in the
time it takes to execute a few SQL statements. The buildfarm reports
indicate that it didn't change.
Both of the observed failures are on Windows machines, where I'm told
that the resolution of gettimeofday() is an abysmal 55msec, so it seems
that the only surprise here is that we haven't seen the failure often
before.
It might be worth trying to improve the resolution of now() on Windows,
though I didn't much care for the ideas Magnus had about how to do that
when I asked him earlier today.
In any case I think this regression test is broken-by-design (and so is
the very similar code in the timestamptz test). Aside from the risk
we see here, there are previously-recognized race conditions in the
statements I omitted: if local midnight passes during the sequence of
statements, you get a failure.
What I'm considering doing is putting a BEGIN/COMMIT around the whole
sequence, which will guarantee that now() does *not* advance, thus
eliminating the midnight gotcha. This would mean that the expected
output of the comparison to 'now' changes from 0 rows to 1 row.
Objections, better ideas?
regards, tom lane
