From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: Avoiding a small risk of failure in timestamp(tz) regression tests
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <14772.1577031054.0@sss.pgh.pa.us>
Date: Sun, 22 Dec 2019 11:11:57 -0500
Message-ID: <14821.1577031117@sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <14772.1577031054.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable

I noticed a buildfarm failure here:

https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=3Dskate&dt=3D2019-=
12-22%2007%3A49%3A22

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D pgsql.build/src/tes=
t/regress/regression.diffs =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
*** /home/pgbf/buildroot/REL_10_STABLE/pgsql.build/src/test/regress/expect=
ed/timestamptz.out	2019-12-13 08:51:47.000000000 +0100
--- /home/pgbf/buildroot/REL_10_STABLE/pgsql.build/src/test/regress/result=
s/timestamptz.out	2019-12-22 09:00:00.000000000 +0100
***************
*** 27,33 ****
  SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with =
time zone 'today';
   one =

  -----
!    1
  (1 row)
  =

  SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with =
time zone 'tomorrow';
--- 27,33 ----
  SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with =
time zone 'today';
   one =

  -----
!    2
  (1 row)
  =

  SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D 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 transacti=
on
-- 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


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="make-timestamp-tests-more-robust.patch";
	charset="us-ascii"
Content-ID: <14772.1577031054.2@sss.pgh.pa.us>
Content-Description: make-timestamp-tests-more-robust.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/ex=
pected/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 f=
or
 -- equality instead.  We can do that by running the test inside a transac=
tion
--- block, within which the value of 'now' shouldn't change.  We also chec=
k
--- 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 transact=
ion
--- block is entered exactly at local midnight; then 'now' and 'today' hav=
e
--- 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 =3D=
 timestamp without time zone
    1
 (1 row)
 =

-SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 =3D timestamp(2) witho=
ut 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 =3D =
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 z=
one);
 -- Test shorthand input values
 -- We can't just "select" the results since they aren't constants; test f=
or
 -- equality instead.  We can do that by running the test inside a transac=
tion
--- block, within which the value of 'now' shouldn't change.  We also chec=
k
--- 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 transact=
ion
--- block is entered exactly at local midnight; then 'now' and 'today' hav=
e
--- 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 =3D=
 timestamp with time zone
    1
 (1 row)
 =

-SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp(2) wit=
h time zone 'now';
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with t=
ime zone 'tomorrow EST';
+ one =

+-----
+   1
+(1 row)
+
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with t=
ime zone 'tomorrow zulu';
  one =

 -----
    1
@@ -50,7 +45,15 @@ SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D=
 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 =3D=
 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/tim=
estamp.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 f=
or
 -- equality instead.  We can do that by running the test inside a transac=
tion
--- block, within which the value of 'now' shouldn't change.  We also chec=
k
--- 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 transact=
ion
--- block is entered exactly at local midnight; then 'now' and 'today' hav=
e
--- 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 =3D timestamp without =
time zone 'today';
 SELECT count(*) AS Three FROM TIMESTAMP_TBL WHERE d1 =3D timestamp withou=
t time zone 'tomorrow';
 SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 =3D timestamp without =
time zone 'yesterday';
-SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 =3D timestamp(2) witho=
ut 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 =3D timestamp(2) witho=
ut 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/t=
imestamptz.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 f=
or
 -- equality instead.  We can do that by running the test inside a transac=
tion
--- block, within which the value of 'now' shouldn't change.  We also chec=
k
--- 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 transact=
ion
--- block is entered exactly at local midnight; then 'now' and 'today' hav=
e
--- 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 =3D timestamp with t=
ime zone 'today';
 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with t=
ime zone 'tomorrow';
 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with t=
ime zone 'yesterday';
-SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp(2) wit=
h time zone 'now';
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with t=
ime zone 'tomorrow EST';
+SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 =3D timestamp with t=
ime 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 =3D timestamp(2) wit=
h 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');

------- =_aaaaaaaaaa0--