From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: Autovacuum-induced regression test instability
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <17304.1555348820.0@sss.pgh.pa.us>
Date: Mon, 15 Apr 2019 13:22:30 -0400
Message-ID: <17428.1555348950@sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <17304.1555348820.1@sss.pgh.pa.us>

In connection with the issue discussed at [1], I tried to run
the core regression tests with extremely aggressive autovacuuming
(I set autovacuum_naptime = 1s, autovacuum_vacuum_threshold = 5,
autovacuum_vacuum_cost_delay = 0).  I found that the timestamp
test tends to fail with diffs caused by unstable row order in
timestamp_tbl.  This is evidently because it does a couple of
DELETEs before inserting the table's final contents; if autovac
comes along at the right time then some of those slots can get
recycled in between insertions.  I'm thinking of committing the
attached patch to prevent this, since in principle such failures
could occur even without hacking the autovac settings.  Thoughts?

			regards, tom lane

[1] https://www.postgresql.org/message-id/15751.1555256860%40sss.pgh.pa.us


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="make-timestamp_tbl-row-ordering-stable.patch";
	charset="us-ascii"
Content-ID: <17304.1555348820.2@sss.pgh.pa.us>
Content-Description: make-timestamp_tbl-row-ordering-stable.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/ex=
pected/timestamp.out
index 4a2fabd..b2b171f 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -74,7 +74,7 @@ SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 =3D t=
imestamp(2) without time z
 (1 row)
 =

 COMMIT;
-DELETE FROM TIMESTAMP_TBL;
+TRUNCATE TIMESTAMP_TBL;
 -- Special values
 INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
 INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/tim=
estamp.sql
index b7957cb..150eb54 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -44,7 +44,7 @@ SELECT pg_sleep(0.1);
 SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 =3D timestamp(2) witho=
ut time zone 'now';
 COMMIT;
 =

-DELETE FROM TIMESTAMP_TBL;
+TRUNCATE TIMESTAMP_TBL;
 =

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

------- =_aaaaaaaaaa0--