Skip to content

Commit 8f735c0

Browse files
tglsfdctglsfdc
committed
Avoid low-probability regression test failures in timestamp[tz] tests.
If the first transaction block in these tests were entered exactly at midnight (California time), they'd report a bogus failure due to 'now' and 'midnight' having the same values. Commit 8c2ac75 had dismissed this as being of negligible probability, but we've now seen it happen in the buildfarm, so let's prevent it. We can get pretty much the same test coverage without an it's-not-midnight assumption by moving the does-'now'-work cases into their own test step. While here, apply commit 47169c2's s/DELETE/TRUNCATE/ change to timestamptz as well as timestamp (not sure why that didn't occur to me at the time; the risk of failure is the same). Back-patch to all supported branches, since the main point is to get rid of potential buildfarm failures. Discussion: https://postgr.es/m/14821.1577031117@sss.pgh.pa.us
1 parent f1a4020 commit 8f735c0

File tree

4 files changed

+65
-61
lines changed

4 files changed

+65
-61
lines changed

‎src/test/regress/expected/timestamp.out

+18-21
Original file line numberDiff line numberDiff line change
@@ -5,20 +5,9 @@ CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone);
55
-- Test shorthand input values
66
-- We can't just "select" the results since they aren't constants; test for
77
-- equality instead. We can do that by running the test inside a transaction
8-
-- block, within which the value of 'now' shouldn't change. We also check
9-
-- that 'now' *does* change over a reasonable interval such as 100 msec.
10-
-- NOTE: it is possible for this part of the test to fail if the transaction
11-
-- block is entered exactly at local midnight; then 'now' and 'today' have
12-
-- the same values and the counts will come out different.
13-
INSERT INTO TIMESTAMP_TBL VALUES ('now');
14-
SELECT pg_sleep(0.1);
15-
pg_sleep
16-
----------
17-
18-
(1 row)
19-
8+
-- block, within which the value of 'now' shouldn't change, and so these
9+
-- related values shouldn't either.
2010
BEGIN;
21-
INSERT INTO TIMESTAMP_TBL VALUES ('now');
2211
INSERT INTO TIMESTAMP_TBL VALUES ('today');
2312
INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
2413
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
@@ -43,15 +32,17 @@ SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone
4332
1
4433
(1 row)
4534

46-
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
47-
one
48-
-----
49-
1
50-
(1 row)
51-
5235
COMMIT;
5336
DELETE FROM TIMESTAMP_TBL;
54-
-- verify uniform transaction time within transaction block
37+
-- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
38+
-- and that it doesn't change over the same interval within a transaction block
39+
INSERT INTO TIMESTAMP_TBL VALUES ('now');
40+
SELECT pg_sleep(0.1);
41+
pg_sleep
42+
----------
43+
44+
(1 row)
45+
5546
BEGIN;
5647
INSERT INTO TIMESTAMP_TBL VALUES ('now');
5748
SELECT pg_sleep(0.1);
@@ -73,8 +64,14 @@ SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time z
7364
2
7465
(1 row)
7566

67+
SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMP_TBL;
68+
three | two
69+
-------+-----
70+
3 | 2
71+
(1 row)
72+
7673
COMMIT;
77-
DELETE FROM TIMESTAMP_TBL;
74+
TRUNCATE TIMESTAMP_TBL;
7875
-- Special values
7976
INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
8077
INSERT INTO TIMESTAMP_TBL VALUES ('infinity');

‎src/test/regress/expected/timestamptz.out

+25-16
Original file line numberDiff line numberDiff line change
@@ -5,20 +5,9 @@ CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
55
-- Test shorthand input values
66
-- We can't just "select" the results since they aren't constants; test for
77
-- equality instead. We can do that by running the test inside a transaction
8-
-- block, within which the value of 'now' shouldn't change. We also check
9-
-- that 'now' *does* change over a reasonable interval such as 100 msec.
10-
-- NOTE: it is possible for this part of the test to fail if the transaction
11-
-- block is entered exactly at local midnight; then 'now' and 'today' have
12-
-- the same values and the counts will come out different.
13-
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
14-
SELECT pg_sleep(0.1);
15-
pg_sleep
16-
----------
17-
18-
(1 row)
19-
8+
-- block, within which the value of 'now' shouldn't change, and so these
9+
-- related values shouldn't either.
2010
BEGIN;
21-
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
2211
INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
2312
INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
2413
INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
@@ -42,15 +31,29 @@ SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone
4231
1
4332
(1 row)
4433

45-
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
34+
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow EST';
35+
one
36+
-----
37+
1
38+
(1 row)
39+
40+
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow zulu';
4641
one
4742
-----
4843
1
4944
(1 row)
5045

5146
COMMIT;
5247
DELETE FROM TIMESTAMPTZ_TBL;
53-
-- verify uniform transaction time within transaction block
48+
-- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
49+
-- and that it doesn't change over the same interval within a transaction block
50+
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
51+
SELECT pg_sleep(0.1);
52+
pg_sleep
53+
----------
54+
55+
(1 row)
56+
5457
BEGIN;
5558
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
5659
SELECT pg_sleep(0.1);
@@ -72,8 +75,14 @@ SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zo
7275
2
7376
(1 row)
7477

78+
SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMPTZ_TBL;
79+
three | two
80+
-------+-----
81+
3 | 2
82+
(1 row)
83+
7584
COMMIT;
76-
DELETE FROM TIMESTAMPTZ_TBL;
85+
TRUNCATE TIMESTAMPTZ_TBL;
7786
-- Special values
7887
INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
7988
INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity');

‎src/test/regress/sql/timestamp.sql

+10-12
Original file line numberDiff line numberDiff line change
@@ -7,18 +7,11 @@ CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone);
77
-- Test shorthand input values
88
-- We can't just "select" the results since they aren't constants; test for
99
-- equality instead. We can do that by running the test inside a transaction
10-
-- block, within which the value of 'now' shouldn't change. We also check
11-
-- that 'now' *does* change over a reasonable interval such as 100 msec.
12-
-- NOTE: it is possible for this part of the test to fail if the transaction
13-
-- block is entered exactly at local midnight; then 'now' and 'today' have
14-
-- the same values and the counts will come out different.
15-
16-
INSERT INTO TIMESTAMP_TBL VALUES ('now');
17-
SELECT pg_sleep(0.1);
10+
-- block, within which the value of 'now' shouldn't change, and so these
11+
-- related values shouldn't either.
1812

1913
BEGIN;
2014

21-
INSERT INTO TIMESTAMP_TBL VALUES ('now');
2215
INSERT INTO TIMESTAMP_TBL VALUES ('today');
2316
INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
2417
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
@@ -29,22 +22,27 @@ INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
2922
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'today';
3023
SELECT count(*) AS Three FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
3124
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'yesterday';
32-
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
3325

3426
COMMIT;
3527

3628
DELETE FROM TIMESTAMP_TBL;
3729

38-
-- verify uniform transaction time within transaction block
30+
-- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
31+
-- and that it doesn't change over the same interval within a transaction block
32+
33+
INSERT INTO TIMESTAMP_TBL VALUES ('now');
34+
SELECT pg_sleep(0.1);
35+
3936
BEGIN;
4037
INSERT INTO TIMESTAMP_TBL VALUES ('now');
4138
SELECT pg_sleep(0.1);
4239
INSERT INTO TIMESTAMP_TBL VALUES ('now');
4340
SELECT pg_sleep(0.1);
4441
SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
42+
SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMP_TBL;
4543
COMMIT;
4644

47-
DELETE FROM TIMESTAMP_TBL;
45+
TRUNCATE TIMESTAMP_TBL;
4846

4947
-- Special values
5048
INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');

‎src/test/regress/sql/timestamptz.sql

+12-12
Original file line numberDiff line numberDiff line change
@@ -7,18 +7,11 @@ CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
77
-- Test shorthand input values
88
-- We can't just "select" the results since they aren't constants; test for
99
-- equality instead. We can do that by running the test inside a transaction
10-
-- block, within which the value of 'now' shouldn't change. We also check
11-
-- that 'now' *does* change over a reasonable interval such as 100 msec.
12-
-- NOTE: it is possible for this part of the test to fail if the transaction
13-
-- block is entered exactly at local midnight; then 'now' and 'today' have
14-
-- the same values and the counts will come out different.
15-
16-
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
17-
SELECT pg_sleep(0.1);
10+
-- block, within which the value of 'now' shouldn't change, and so these
11+
-- related values shouldn't either.
1812

1913
BEGIN;
2014

21-
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
2215
INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
2316
INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
2417
INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
@@ -28,22 +21,29 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu');
2821
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today';
2922
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow';
3023
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'yesterday';
31-
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
24+
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow EST';
25+
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow zulu';
3226

3327
COMMIT;
3428

3529
DELETE FROM TIMESTAMPTZ_TBL;
3630

37-
-- verify uniform transaction time within transaction block
31+
-- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
32+
-- and that it doesn't change over the same interval within a transaction block
33+
34+
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
35+
SELECT pg_sleep(0.1);
36+
3837
BEGIN;
3938
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
4039
SELECT pg_sleep(0.1);
4140
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
4241
SELECT pg_sleep(0.1);
4342
SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
43+
SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMPTZ_TBL;
4444
COMMIT;
4545

46-
DELETE FROM TIMESTAMPTZ_TBL;
46+
TRUNCATE TIMESTAMPTZ_TBL;
4747

4848
-- Special values
4949
INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');

0 commit comments

Comments
 (0)