From 1c5c70df45714f38c033bb1a272d8db4f2dc8a2f Mon Sep 17 00:00:00 2001 From: Tom Lane 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.40.0