From 1c5c70df45714f38c033bb1a272d8db4f2dc8a2f Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 12 Dec 2014 15:49:03 -0500
Subject: [PATCH] Avoid instability in output of new REINDEX SCHEMA test.
The planner seems to like to do this join query as a hash join, making
the output ordering machine-dependent; worse, it's a hash on OIDs, so
that it's a bit astonishing that the result doesn't change from run to
run even on one machine. Add an ORDER BY to get consistent results.
Per buildfarm.
I also suppressed output from the final DROP SCHEMA CASCADE, to avoid
occasional failures similar to those fixed in commit 81d815dc3ed74a7d.
That hasn't been observed in the buildfarm yet, but it seems likely
to happen in future if we leave it as-is.
---
src/test/regress/expected/create_index.out | 36 ++++++++++------------
src/test/regress/sql/create_index.sql | 15 +++++----
2 files changed, 25 insertions(+), 26 deletions(-)
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 97f415c307..5603817c77 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2859,29 +2859,30 @@ SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid
FROM pg_class where oid in
(select indexrelid from pg_index where indrelid in
(select reltoastrelid from reindex_before where reltoastrelid > 0));
- REINDEX SCHEMA schema_to_reindex;
+REINDEX SCHEMA schema_to_reindex;
CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind
FROM pg_class
where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
-SELECT b.relname,
- b.relkind,
- CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
- ELSE 'relfilenode has changed' END
-FROM reindex_before b JOIN pg_class a ON b.oid = a.oid;
+SELECT b.relname,
+ b.relkind,
+ CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
+ ELSE 'relfilenode has changed' END
+ FROM reindex_before b JOIN pg_class a ON b.oid = a.oid
+ ORDER BY 1;
relname | relkind | case
----------------------+---------+--------------------------
- table1_col1_seq | S | relfilenode is unchanged
+ matview | m | relfilenode is unchanged
+ matview_col1_idx | i | relfilenode has changed
+ pg_toast_TABLE | t | relfilenode is unchanged
+ pg_toast_TABLE_index | i | relfilenode has changed
table1 | r | relfilenode is unchanged
+ table1_col1_seq | S | relfilenode is unchanged
table1_pkey | i | relfilenode has changed
- table2_col1_seq | S | relfilenode is unchanged
table2 | r | relfilenode is unchanged
- table2_pkey | i | relfilenode has changed
+ table2_col1_seq | S | relfilenode is unchanged
table2_col2_idx | i | relfilenode has changed
- matview | m | relfilenode is unchanged
- matview_col1_idx | i | relfilenode has changed
+ table2_pkey | i | relfilenode has changed
view | v | relfilenode is unchanged
- pg_toast_TABLE | t | relfilenode is unchanged
- pg_toast_TABLE_index | i | relfilenode has changed
(12 rows)
REINDEX SCHEMA schema_to_reindex;
@@ -2897,11 +2898,6 @@ ERROR: must be owner of schema schema_to_reindex
-- Clean up
RESET ROLE;
DROP ROLE regression_reindexuser;
+SET client_min_messages TO 'warning';
DROP SCHEMA schema_to_reindex CASCADE;
-NOTICE: drop cascades to 6 other objects
-DETAIL: drop cascades to table table1
-drop cascades to table table2
-drop cascades to materialized view matview
-drop cascades to view view
-drop cascades to table reindex_before
-drop cascades to table reindex_after
+RESET client_min_messages;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 498fce0042..f779fa0de1 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -992,15 +992,16 @@ SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid
FROM pg_class where oid in
(select indexrelid from pg_index where indrelid in
(select reltoastrelid from reindex_before where reltoastrelid > 0));
- REINDEX SCHEMA schema_to_reindex;
+REINDEX SCHEMA schema_to_reindex;
CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind
FROM pg_class
where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
-SELECT b.relname,
- b.relkind,
- CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
- ELSE 'relfilenode has changed' END
-FROM reindex_before b JOIN pg_class a ON b.oid = a.oid;
+SELECT b.relname,
+ b.relkind,
+ CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
+ ELSE 'relfilenode has changed' END
+ FROM reindex_before b JOIN pg_class a ON b.oid = a.oid
+ ORDER BY 1;
REINDEX SCHEMA schema_to_reindex;
BEGIN;
REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction
@@ -1014,4 +1015,6 @@ REINDEX SCHEMA schema_to_reindex;
-- Clean up
RESET ROLE;
DROP ROLE regression_reindexuser;
+SET client_min_messages TO 'warning';
DROP SCHEMA schema_to_reindex CASCADE;
+RESET client_min_messages;
--
2.49.0