I noticed a buildfarm failure here:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skate&dt=2019-12-22%2007%3A49%3A22
================== pgsql.build/src/test/regress/regression.diffs ==================
*** /home/pgbf/buildroot/REL_10_STABLE/pgsql.build/src/test/regress/expected/timestamptz.out 2019-12-13
08:51:47.000000000+0100
--- /home/pgbf/buildroot/REL_10_STABLE/pgsql.build/src/test/regress/results/timestamptz.out 2019-12-22
09:00:00.000000000+0100
***************
*** 27,33 ****
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today';
one
-----
! 1
(1 row)
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow';
--- 27,33 ----
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today';
one
-----
! 2
(1 row)
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow';
Judging by the reported timestamp on the results file, this is an instance
of the problem mentioned in the comments in timestamptz.sql:
-- NOTE: it is possible for this part of the test to fail if the transaction
-- block is entered exactly at local midnight; then 'now' and 'today' have
-- the same values and the counts will come out different.
On most machines it'd be pretty hard to hit that window; I speculate that
"skate" has got a very low-resolution system clock, making the window
larger. Nonetheless, a test that's got designed-in failure modes is
annoying. We can dodge this by separating the test for "now" from the
tests for the today/tomorrow/etc input strings, as attached.
Any objections?
regards, tom lane
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 39a4d49..5f97505 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -5,20 +5,9 @@ CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone);
-- Test shorthand input values
-- We can't just "select" the results since they aren't constants; test for
-- equality instead. We can do that by running the test inside a transaction
--- block, within which the value of 'now' shouldn't change. We also check
--- that 'now' *does* change over a reasonable interval such as 100 msec.
--- NOTE: it is possible for this part of the test to fail if the transaction
--- block is entered exactly at local midnight; then 'now' and 'today' have
--- the same values and the counts will come out different.
-INSERT INTO TIMESTAMP_TBL VALUES ('now');
-SELECT pg_sleep(0.1);
- pg_sleep
-----------
-
-(1 row)
-
+-- block, within which the value of 'now' shouldn't change, and so these
+-- related values shouldn't either.
BEGIN;
-INSERT INTO TIMESTAMP_TBL VALUES ('now');
INSERT INTO TIMESTAMP_TBL VALUES ('today');
INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
@@ -43,15 +32,17 @@ SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone
1
(1 row)
-SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
- one
------
- 1
-(1 row)
-
COMMIT;
DELETE FROM TIMESTAMP_TBL;
--- verify uniform transaction time within transaction block
+-- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
+-- and that it doesn't change over the same interval within a transaction block
+INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+ pg_sleep
+----------
+
+(1 row)
+
BEGIN;
INSERT INTO TIMESTAMP_TBL VALUES ('now');
SELECT pg_sleep(0.1);
@@ -73,6 +64,12 @@ SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time z
2
(1 row)
+SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMP_TBL;
+ three | two
+-------+-----
+ 3 | 2
+(1 row)
+
COMMIT;
TRUNCATE TIMESTAMP_TBL;
-- Special values
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index bb89910..639b503 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -5,20 +5,9 @@ CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
-- Test shorthand input values
-- We can't just "select" the results since they aren't constants; test for
-- equality instead. We can do that by running the test inside a transaction
--- block, within which the value of 'now' shouldn't change. We also check
--- that 'now' *does* change over a reasonable interval such as 100 msec.
--- NOTE: it is possible for this part of the test to fail if the transaction
--- block is entered exactly at local midnight; then 'now' and 'today' have
--- the same values and the counts will come out different.
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
-SELECT pg_sleep(0.1);
- pg_sleep
-----------
-
-(1 row)
-
+-- block, within which the value of 'now' shouldn't change, and so these
+-- related values shouldn't either.
BEGIN;
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
@@ -42,7 +31,13 @@ SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone
1
(1 row)
-SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow EST';
+ one
+-----
+ 1
+(1 row)
+
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow zulu';
one
-----
1
@@ -50,7 +45,15 @@ SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zo
COMMIT;
DELETE FROM TIMESTAMPTZ_TBL;
--- verify uniform transaction time within transaction block
+-- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
+-- and that it doesn't change over the same interval within a transaction block
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+ pg_sleep
+----------
+
+(1 row)
+
BEGIN;
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
SELECT pg_sleep(0.1);
@@ -72,8 +75,14 @@ SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zo
2
(1 row)
+SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMPTZ_TBL;
+ three | two
+-------+-----
+ 3 | 2
+(1 row)
+
COMMIT;
-DELETE FROM TIMESTAMPTZ_TBL;
+TRUNCATE TIMESTAMPTZ_TBL;
-- Special values
INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity');
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 0d4d465..7b58c3c 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -7,18 +7,11 @@ CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone);
-- Test shorthand input values
-- We can't just "select" the results since they aren't constants; test for
-- equality instead. We can do that by running the test inside a transaction
--- block, within which the value of 'now' shouldn't change. We also check
--- that 'now' *does* change over a reasonable interval such as 100 msec.
--- NOTE: it is possible for this part of the test to fail if the transaction
--- block is entered exactly at local midnight; then 'now' and 'today' have
--- the same values and the counts will come out different.
-
-INSERT INTO TIMESTAMP_TBL VALUES ('now');
-SELECT pg_sleep(0.1);
+-- block, within which the value of 'now' shouldn't change, and so these
+-- related values shouldn't either.
BEGIN;
-INSERT INTO TIMESTAMP_TBL VALUES ('now');
INSERT INTO TIMESTAMP_TBL VALUES ('today');
INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
@@ -29,19 +22,24 @@ INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'today';
SELECT count(*) AS Three FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'yesterday';
-SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
COMMIT;
DELETE FROM TIMESTAMP_TBL;
--- verify uniform transaction time within transaction block
+-- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
+-- and that it doesn't change over the same interval within a transaction block
+
+INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+
BEGIN;
INSERT INTO TIMESTAMP_TBL VALUES ('now');
SELECT pg_sleep(0.1);
INSERT INTO TIMESTAMP_TBL VALUES ('now');
SELECT pg_sleep(0.1);
SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
+SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMP_TBL;
COMMIT;
TRUNCATE TIMESTAMP_TBL;
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 67b4a7d..300302d 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -7,18 +7,11 @@ CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
-- Test shorthand input values
-- We can't just "select" the results since they aren't constants; test for
-- equality instead. We can do that by running the test inside a transaction
--- block, within which the value of 'now' shouldn't change. We also check
--- that 'now' *does* change over a reasonable interval such as 100 msec.
--- NOTE: it is possible for this part of the test to fail if the transaction
--- block is entered exactly at local midnight; then 'now' and 'today' have
--- the same values and the counts will come out different.
-
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
-SELECT pg_sleep(0.1);
+-- block, within which the value of 'now' shouldn't change, and so these
+-- related values shouldn't either.
BEGIN;
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
@@ -28,22 +21,29 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu');
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today';
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow';
SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'yesterday';
-SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow EST';
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow zulu';
COMMIT;
DELETE FROM TIMESTAMPTZ_TBL;
--- verify uniform transaction time within transaction block
+-- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
+-- and that it doesn't change over the same interval within a transaction block
+
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
+SELECT pg_sleep(0.1);
+
BEGIN;
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
SELECT pg_sleep(0.1);
INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
SELECT pg_sleep(0.1);
SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
+SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMPTZ_TBL;
COMMIT;
-DELETE FROM TIMESTAMPTZ_TBL;
+TRUNCATE TIMESTAMPTZ_TBL;
-- Special values
INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
