Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb265aa1

Browse files
committed
Avoid low-probability regression test failures in timestamp[tz] tests.
If the first transaction block in these tests were entered exactlyat midnight (California time), they'd report a bogus failure dueto 'now' and 'midnight' having the same values. Commit8c2ac75had dismissed this as being of negligible probability, but we'venow seen it happen in the buildfarm, so let's prevent it. We canget pretty much the same test coverage without an it's-not-midnightassumption by moving the does-'now'-work cases into their own test step.While here, apply commit47169c2's s/DELETE/TRUNCATE/ change totimestamptz as well as timestamp (not sure why that didn'toccur to me at the time; the risk of failure is the same).Back-patch to all supported branches, since the main point isto get rid of potential buildfarm failures.Discussion:https://postgr.es/m/14821.1577031117@sss.pgh.pa.us
1 parent127ccb3 commitb265aa1

File tree

4 files changed

+63
-59
lines changed

4 files changed

+63
-59
lines changed

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

Lines changed: 17 additions & 20 deletions
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,6 +64,12 @@ 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;
7774
TRUNCATE TIMESTAMP_TBL;
7875
-- Special values

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

Lines changed: 25 additions & 16 deletions
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

Lines changed: 9 additions & 11 deletions
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_TBLVALUES ('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_TBLVALUES ('now');
2215
INSERT INTO TIMESTAMP_TBLVALUES ('today');
2316
INSERT INTO TIMESTAMP_TBLVALUES ('yesterday');
2417
INSERT INTO TIMESTAMP_TBLVALUES ('tomorrow');
@@ -29,19 +22,24 @@ INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
2922
SELECTcount(*)AS OneFROM TIMESTAMP_TBLWHERE d1=timestamp without time zone'today';
3023
SELECTcount(*)AS ThreeFROM TIMESTAMP_TBLWHERE d1=timestamp without time zone'tomorrow';
3124
SELECTcount(*)AS OneFROM TIMESTAMP_TBLWHERE d1=timestamp without time zone'yesterday';
32-
SELECTcount(*)AS OneFROM TIMESTAMP_TBLWHERE d1=timestamp(2) without time zone'now';
3325

3426
COMMIT;
3527

3628
DELETEFROM 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_TBLVALUES ('now');
34+
SELECT pg_sleep(0.1);
35+
3936
BEGIN;
4037
INSERT INTO TIMESTAMP_TBLVALUES ('now');
4138
SELECT pg_sleep(0.1);
4239
INSERT INTO TIMESTAMP_TBLVALUES ('now');
4340
SELECT pg_sleep(0.1);
4441
SELECTcount(*)AS twoFROM TIMESTAMP_TBLWHERE d1=timestamp(2) without time zone'now';
42+
SELECTcount(d1)AS three,count(DISTINCT d1)AS twoFROM TIMESTAMP_TBL;
4543
COMMIT;
4644

4745
TRUNCATE TIMESTAMP_TBL;

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

Lines changed: 12 additions & 12 deletions
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_TBLVALUES ('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_TBLVALUES ('now');
2215
INSERT INTO TIMESTAMPTZ_TBLVALUES ('today');
2316
INSERT INTO TIMESTAMPTZ_TBLVALUES ('yesterday');
2417
INSERT INTO TIMESTAMPTZ_TBLVALUES ('tomorrow');
@@ -28,22 +21,29 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu');
2821
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'today';
2922
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'tomorrow';
3023
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'yesterday';
31-
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp(2) with time zone'now';
24+
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'tomorrow EST';
25+
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'tomorrow zulu';
3226

3327
COMMIT;
3428

3529
DELETEFROM 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_TBLVALUES ('now');
35+
SELECT pg_sleep(0.1);
36+
3837
BEGIN;
3938
INSERT INTO TIMESTAMPTZ_TBLVALUES ('now');
4039
SELECT pg_sleep(0.1);
4140
INSERT INTO TIMESTAMPTZ_TBLVALUES ('now');
4241
SELECT pg_sleep(0.1);
4342
SELECTcount(*)AS twoFROM TIMESTAMPTZ_TBLWHERE d1=timestamp(2) with time zone'now';
43+
SELECTcount(d1)AS three,count(DISTINCT d1)AS twoFROM TIMESTAMPTZ_TBL;
4444
COMMIT;
4545

46-
DELETEFROM TIMESTAMPTZ_TBL;
46+
TRUNCATE TIMESTAMPTZ_TBL;
4747

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp