Repository: GoogleCloudPlatform/pgadapter
Pull request #53 (2022-03-06 18:50:47)

test: specify select columns for \di meta command
Specify the columns to select for the `\di` metadata command to prevent failures when new columns are added to the `information_schema.indexes` table. Fixes the current (flaky) build failures for other PRs.


Patch:
diff --git a/src/main/resources/metadata/default_command_metadata.json b/src/main/resources/metadata/default_command_metadata.json
index 634f2a0c..4ef94f7c 100644
--- a/src/main/resources/metadata/default_command_metadata.json
+++ b/src/main/resources/metadata/default_command_metadata.json
@@ -56,13 +56,13 @@
     },
     {
       "input_pattern": "^SELECT n\\.nspname as \"[^\"]*\",[ \n]+c\\.relname as \"[^\"]*\",[ \n]+CASE c\\.relkind (WHEN '.' THEN '[^']*' )+END as \"[^\"]*\",[ \n]+pg_catalog\\.pg_get_userbyid\\(c\\.relowner\\) as \"[^\"]*\",[ \n]+c2\\.relname as \"[^\"]*\"\nFROM pg_catalog\\.pg_class c[ \n]+LEFT JOIN pg_catalog\\.pg_namespace n ON n\\.oid = c\\.relnamespace[ \n]+LEFT JOIN pg_catalog\\.pg_index i ON i\\.indexrelid = c\\.oid[ \n]+LEFT JOIN pg_catalog\\.pg_class c2 ON i\\.indrelid = c2\\.oid\nWHERE c\\.relkind IN \\('i'(?:,'I')?,''\\)[ \n]+AND n\\.nspname <> 'pg_catalog'[ \n]+AND (?:n\\.nspname <> 'information_schema'[ \n]+AND )?n\\.nspname !~ '\\^pg_toast'[ \n]+AND (?:n\\.nspname <> 'information_schema'[ \n]+AND )?pg_catalog\\.pg_table_is_visible\\(c\\.oid\\)\nORDER BY 1,2;$",
-      "output_pattern": "SELECT * FROM information_schema.indexes;",
+      "output_pattern": "SELECT table_catalog, table_schema, table_name, index_name, index_type, parent_table_name, is_unique, is_null_filtered, index_state, spanner_is_managed FROM information_schema.indexes;",
       "matcher_array": [],
       "blurb": "This command is equivalent to the PSQL \\di meta-command."
     },
     {
       "input_pattern": "^SELECT n\\.nspname as \"[^\"]*\",[ \n]+c\\.relname as \"[^\"]*\",[ \n]+CASE c\\.relkind (WHEN '.' THEN '[^']*' )+END as \"[^\"]*\",[ \n]+pg_catalog\\.pg_get_userbyid\\(c\\.relowner\\) as \"[^\"]*\",[ \n]+c2\\.relname as \"[^\"]*\"\nFROM pg_catalog\\.pg_class c[ \n]+LEFT JOIN pg_catalog\\.pg_namespace n ON n\\.oid = c\\.relnamespace[ \n]+LEFT JOIN pg_catalog\\.pg_index i ON i\\.indexrelid = c\\.oid[ \n]+LEFT JOIN pg_catalog\\.pg_class c2 ON i\\.indrelid = c2\\.oid\nWHERE c\\.relkind IN \\('i'(?:,'I')?,'s',''\\)[ \n]+AND (?:n\\.nspname !~ '\\^pg_toast'[ \n]+AND )?c\\.relname OPERATOR\\(pg_catalog\\.~\\) '\\^\\((?<indexname>.*)\\)\\$'[ \n]+AND pg_catalog\\.pg_table_is_visible\\(c\\.oid\\)\nORDER BY 1,2;$",
-      "output_pattern": "SELECT * FROM information_schema.indexes WHERE LOWER(index_name) = LOWER('%s');",
+      "output_pattern": "SELECT table_catalog, table_schema, table_name, index_name, index_type, parent_table_name, is_unique, is_null_filtered, index_state, spanner_is_managed FROM information_schema.indexes WHERE LOWER(index_name) = LOWER('%s');",
       "matcher_array": [
         "indexname"
       ],


Test patch:
diff --git a/.ci/e2e-expected/backslash-di.txt b/.ci/e2e-expected/backslash-di.txt
index 5d15c8ef..2f6531ae 100644
--- a/.ci/e2e-expected/backslash-di.txt
+++ b/.ci/e2e-expected/backslash-di.txt
@@ -1,5 +1,5 @@
                                                                                    List of relations
- table_catalog |    table_schema    |           table_name            | index_name  | index_type  | parent_table_name | is_unique | is_null_filtered | index_state | spanner_is_managed 
+ table_catalog |    table_schema    |           table_name            | index_name  | index_type  | parent_table_name | is_unique | is_null_filtered | index_state | spanner_is_managed
 +++++++++
  testdb_e2e_psql_v?? | public             | users                           | PRIMARY_KEY | PRIMARY_KEY |                   | YES       | NO               |             | NO
  testdb_e2e_psql_v?? | information_schema | information_schema_catalog_name | PRIMARY_KEY | PRIMARY_KEY |                   | YES       | NO               |             | NO
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/PSQLTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/PSQLTest.java
index cbe47aa6..28153443 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/PSQLTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/PSQLTest.java
@@ -437,7 +437,8 @@ public class PSQLTest {
             + "      AND n.nspname !~ '^pg_toast'\n"
             + "  AND pg_catalog.pg_table_is_visible(c.oid)\n"
             + "ORDER BY 1,2;";
-    String result = "SELECT * FROM information_schema.indexes;";
+    String result =
+        "SELECT table_catalog, table_schema, table_name, index_name, index_type, parent_table_name, is_unique, is_null_filtered, index_state, spanner_is_managed FROM information_schema.indexes;";
 
     MatcherStatement matcherStatement = new MatcherStatement(sql, connectionHandler);
 
@@ -466,7 +467,8 @@ public class PSQLTest {
             + "  AND pg_catalog.pg_table_is_visible(c.oid)\n"
             + "ORDER BY 1,2;";
     String result =
-        "SELECT * FROM information_schema.indexes WHERE LOWER(index_name) =" + " LOWER('index');";
+        "SELECT table_catalog, table_schema, table_name, index_name, index_type, parent_table_name, is_unique, is_null_filtered, index_state, spanner_is_managed FROM information_schema.indexes WHERE LOWER(index_name) ="
+            + " LOWER('index');";
 
     MatcherStatement matcherStatement = new MatcherStatement(sql, connectionHandler);
 
@@ -495,7 +497,7 @@ public class PSQLTest {
             + "  AND pg_catalog.pg_table_is_visible(c.oid)\n"
             + "ORDER BY 1,2;";
     String result =
-        "SELECT * FROM information_schema.indexes WHERE LOWER(index_name) ="
+        "SELECT table_catalog, table_schema, table_name, index_name, index_type, parent_table_name, is_unique, is_null_filtered, index_state, spanner_is_managed FROM information_schema.indexes WHERE LOWER(index_name) ="
             + " LOWER('bobby\\'; DROP TABLE USERS; SELECT\\'');";
 
     MatcherStatement matcherStatement = new MatcherStatement(sql, connectionHandler);