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

Commit8c2ac75

Browse files
committed
Adjust timestamp regression tests to prevent two low-probability failure
cases. Recent buildfarm experience shows that it is sometimes possibleto execute several SQL commands in less time than the granularity ofWindows' not-very-high-resolution gettimeofday(), leading to a failurebecause the tests expect the value of now() to change and it doesn't.Also, it was recognized some time ago that the same area of the testscould fail if local midnight passes between the insertion and the checkingof the values for 'yesterday', 'tomorrow', etc. Clean all this up perideas from myself and Greg Stark.There remains a window for failure if the transaction block is enteredexactly at local midnight (so that 'now' and 'today' have the same value),but that seems low-probability enough to live with.Since the point of this change is mostly to eliminate buildfarm noise,back-patch to all versions we are still actively testing.
1 parentd82e7c8 commit8c2ac75

File tree

4 files changed

+126
-39
lines changed

4 files changed

+126
-39
lines changed

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

Lines changed: 42 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,18 +1,28 @@
11
--
22
-- TIMESTAMP
33
--
4-
CREATE TABLE TIMESTAMP_TBL ( d1 timestamp(2) without time zone);
5-
-- Shorthand values
6-
-- Not directly usable for regression testing since these are not constants.
7-
-- So, just try to test parser and hope for the best - thomas 97/04/26
8-
-- NB: could get a failure if local midnight passes during the next few
9-
-- statements.
4+
CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone);
5+
-- Test shorthand input values
6+
-- We can't just "select" the results since they aren't constants; test for
7+
-- 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+
20+
BEGIN;
1021
INSERT INTO TIMESTAMP_TBL VALUES ('now');
11-
INSERT INTO TIMESTAMP_TBL VALUES ('current');
12-
ERROR: date/time value "current" is no longer supported
1322
INSERT INTO TIMESTAMP_TBL VALUES ('today');
1423
INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
1524
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
25+
-- time zone should be ignored by this data type
1626
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow EST');
1727
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
1828
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'today';
@@ -21,10 +31,10 @@ SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone
2131
1
2232
(1 row)
2333

24-
SELECT count(*) ASOne FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
25-
one
26-
-----
27-
3
34+
SELECT count(*) ASThree FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
35+
three
36+
-------
37+
3
2838
(1 row)
2939

3040
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'yesterday';
@@ -33,24 +43,37 @@ SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone
3343
1
3444
(1 row)
3545

36-
SELECT count(*) ASNone FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'now';
37-
none
38-
------
39-
0
46+
SELECT count(*) ASOne FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
47+
one
48+
-----
49+
1
4050
(1 row)
4151

52+
COMMIT;
4253
DELETE FROM TIMESTAMP_TBL;
4354
-- verify uniform transaction time within transaction block
4455
BEGIN;
4556
INSERT INTO TIMESTAMP_TBL VALUES ('now');
57+
SELECT pg_sleep(0.1);
58+
pg_sleep
59+
----------
60+
61+
(1 row)
62+
4663
INSERT INTO TIMESTAMP_TBL VALUES ('now');
64+
SELECT pg_sleep(0.1);
65+
pg_sleep
66+
----------
67+
68+
(1 row)
69+
4770
SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
4871
two
4972
-----
5073
2
5174
(1 row)
5275

53-
END;
76+
COMMIT;
5477
DELETE FROM TIMESTAMP_TBL;
5578
-- Special values
5679
INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
@@ -59,6 +82,8 @@ INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
5982
-- Obsolete special values
6083
INSERT INTO TIMESTAMP_TBL VALUES ('invalid');
6184
ERROR: date/time value "invalid" is no longer supported
85+
INSERT INTO TIMESTAMP_TBL VALUES ('current');
86+
ERROR: date/time value "current" is no longer supported
6287
-- Postgres v6.0 standard output format
6388
INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
6489
INSERT INTO TIMESTAMP_TBL VALUES ('Invalid Abstime');

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

Lines changed: 37 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,24 @@
11
--
22
-- TIMESTAMPTZ
33
--
4-
CREATE TABLE TIMESTAMPTZ_TBL ( d1 timestamp(2) with time zone);
4+
CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
5+
-- Test shorthand input values
6+
-- We can't just "select" the results since they aren't constants; test for
7+
-- 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+
20+
BEGIN;
521
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
6-
INSERT INTO TIMESTAMPTZ_TBL VALUES ('current');
7-
ERROR: date/time value "current" is no longer supported
822
INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
923
INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
1024
INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
@@ -28,24 +42,37 @@ SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone
2842
1
2943
(1 row)
3044

31-
SELECT count(*) ASNone FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'now';
32-
none
33-
------
34-
0
45+
SELECT count(*) ASOne FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
46+
one
47+
-----
48+
1
3549
(1 row)
3650

51+
COMMIT;
3752
DELETE FROM TIMESTAMPTZ_TBL;
3853
-- verify uniform transaction time within transaction block
3954
BEGIN;
4055
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
56+
SELECT pg_sleep(0.1);
57+
pg_sleep
58+
----------
59+
60+
(1 row)
61+
4162
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
63+
SELECT pg_sleep(0.1);
64+
pg_sleep
65+
----------
66+
67+
(1 row)
68+
4269
SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
4370
two
4471
-----
4572
2
4673
(1 row)
4774

48-
END;
75+
COMMIT;
4976
DELETE FROM TIMESTAMPTZ_TBL;
5077
-- Special values
5178
INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
@@ -54,6 +81,8 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('epoch');
5481
-- Obsolete special values
5582
INSERT INTO TIMESTAMPTZ_TBL VALUES ('invalid');
5683
ERROR: date/time value "invalid" is no longer supported
84+
INSERT INTO TIMESTAMPTZ_TBL VALUES ('current');
85+
ERROR: date/time value "current" is no longer supported
5786
-- Postgres v6.0 standard output format
5887
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
5988
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Invalid Abstime');

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

Lines changed: 24 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2,35 +2,48 @@
22
-- TIMESTAMP
33
--
44

5-
CREATETABLETIMESTAMP_TBL (d1timestamp(2) without time zone);
5+
CREATETABLETIMESTAMP_TBL (d1timestamp(2) without time zone);
66

7-
-- Shorthand values
8-
-- Not directly usable for regression testing since these are not constants.
9-
-- So, just try to test parser and hope for the best - thomas 97/04/26
10-
-- NB: could get a failure if local midnight passes during the next few
11-
-- statements.
7+
-- Test shorthand input values
8+
-- We can't just "select" the results since they aren't constants; test for
9+
-- 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);
18+
19+
BEGIN;
1220

1321
INSERT INTO TIMESTAMP_TBLVALUES ('now');
14-
INSERT INTO TIMESTAMP_TBLVALUES ('current');
1522
INSERT INTO TIMESTAMP_TBLVALUES ('today');
1623
INSERT INTO TIMESTAMP_TBLVALUES ('yesterday');
1724
INSERT INTO TIMESTAMP_TBLVALUES ('tomorrow');
25+
-- time zone should be ignored by this data type
1826
INSERT INTO TIMESTAMP_TBLVALUES ('tomorrow EST');
1927
INSERT INTO TIMESTAMP_TBLVALUES ('tomorrow zulu');
2028

2129
SELECTcount(*)AS OneFROM TIMESTAMP_TBLWHERE d1=timestamp without time zone'today';
22-
SELECTcount(*)ASOneFROM TIMESTAMP_TBLWHERE d1=timestamp without time zone'tomorrow';
30+
SELECTcount(*)ASThreeFROM TIMESTAMP_TBLWHERE d1=timestamp without time zone'tomorrow';
2331
SELECTcount(*)AS OneFROM TIMESTAMP_TBLWHERE d1=timestamp without time zone'yesterday';
24-
SELECTcount(*)AS NoneFROM TIMESTAMP_TBLWHERE d1=timestamp without time zone'now';
32+
SELECTcount(*)AS OneFROM TIMESTAMP_TBLWHERE d1=timestamp(2) without time zone'now';
33+
34+
COMMIT;
2535

2636
DELETEFROM TIMESTAMP_TBL;
2737

2838
-- verify uniform transaction time within transaction block
2939
BEGIN;
3040
INSERT INTO TIMESTAMP_TBLVALUES ('now');
41+
SELECT pg_sleep(0.1);
3142
INSERT INTO TIMESTAMP_TBLVALUES ('now');
43+
SELECT pg_sleep(0.1);
3244
SELECTcount(*)AS twoFROM TIMESTAMP_TBLWHERE d1=timestamp(2) without time zone'now';
33-
END;
45+
COMMIT;
46+
3447
DELETEFROM TIMESTAMP_TBL;
3548

3649
-- Special values
@@ -39,6 +52,7 @@ INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
3952
INSERT INTO TIMESTAMP_TBLVALUES ('epoch');
4053
-- Obsolete special values
4154
INSERT INTO TIMESTAMP_TBLVALUES ('invalid');
55+
INSERT INTO TIMESTAMP_TBLVALUES ('current');
4256

4357
-- Postgres v6.0 standard output format
4458
INSERT INTO TIMESTAMP_TBLVALUES ('Mon Feb 10 17:32:01 1997 PST');

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

Lines changed: 23 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2,10 +2,23 @@
22
-- TIMESTAMPTZ
33
--
44

5-
CREATETABLETIMESTAMPTZ_TBL ( d1timestamp(2) with time zone);
5+
CREATETABLETIMESTAMPTZ_TBL (d1timestamp(2) with time zone);
6+
7+
-- Test shorthand input values
8+
-- We can't just "select" the results since they aren't constants; test for
9+
-- 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);
18+
19+
BEGIN;
620

721
INSERT INTO TIMESTAMPTZ_TBLVALUES ('now');
8-
INSERT INTO TIMESTAMPTZ_TBLVALUES ('current');
922
INSERT INTO TIMESTAMPTZ_TBLVALUES ('today');
1023
INSERT INTO TIMESTAMPTZ_TBLVALUES ('yesterday');
1124
INSERT INTO TIMESTAMPTZ_TBLVALUES ('tomorrow');
@@ -15,16 +28,21 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu');
1528
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'today';
1629
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'tomorrow';
1730
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'yesterday';
18-
SELECTcount(*)AS NoneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp with time zone'now';
31+
SELECTcount(*)AS OneFROM TIMESTAMPTZ_TBLWHERE d1=timestamp(2) with time zone'now';
32+
33+
COMMIT;
1934

2035
DELETEFROM TIMESTAMPTZ_TBL;
2136

2237
-- verify uniform transaction time within transaction block
2338
BEGIN;
2439
INSERT INTO TIMESTAMPTZ_TBLVALUES ('now');
40+
SELECT pg_sleep(0.1);
2541
INSERT INTO TIMESTAMPTZ_TBLVALUES ('now');
42+
SELECT pg_sleep(0.1);
2643
SELECTcount(*)AS twoFROM TIMESTAMPTZ_TBLWHERE d1=timestamp(2) with time zone'now';
27-
END;
44+
COMMIT;
45+
2846
DELETEFROM TIMESTAMPTZ_TBL;
2947

3048
-- Special values
@@ -33,6 +51,7 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity');
3351
INSERT INTO TIMESTAMPTZ_TBLVALUES ('epoch');
3452
-- Obsolete special values
3553
INSERT INTO TIMESTAMPTZ_TBLVALUES ('invalid');
54+
INSERT INTO TIMESTAMPTZ_TBLVALUES ('current');
3655

3756
-- Postgres v6.0 standard output format
3857
INSERT INTO TIMESTAMPTZ_TBLVALUES ('Mon Feb 10 17:32:01 1997 PST');

0 commit comments

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