Issue xo#235. Fix wrong index access method in PostgreSQL \d \di slash

commands.
fduartef · May 31, 2023 · 3091e86 · 3091e86
1 parent 16038db
commit 3091e86
Show file tree

Hide file tree

Showing 3 changed files with 93 additions and 14 deletions.
diff --git a/drivers/metadata/postgres/metadata.go b/drivers/metadata/postgres/metadata.go
@@ -277,11 +277,17 @@ SELECT
   c.relname as "Name",
   CASE i.indisprimary WHEN TRUE THEN 'YES' ELSE 'NO' END,
   CASE i.indisunique WHEN TRUE THEN 'YES' ELSE 'NO' END,
-  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type"
+  COALESCE(am.amname, 
+  	CASE c.relkind 
+		WHEN 'i' THEN 'index' 
+		WHEN 'I' THEN 'partitioned index' 
+	END
+   ) as "Type"
 FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
-     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid`
+     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
+     LEFT JOIN pg_am am ON am.oid=c.relam`
 	conds := []string{
 		"c.relkind IN ('i','I','')",
 		"n.nspname !~ '^pg_toast'",

diff --git a/drivers/metadata/postgres/metadata_test.go b/drivers/metadata/postgres/metadata_test.go
@@ -215,3 +215,76 @@ func TestColumns(t *testing.T) {
 		}
 	}
 }
+
+func TestIndexes(t *testing.T) {
+	schema := "public"
+	table := "tmp_table"
+
+	tests := []struct {
+		indexType string
+		want      string
+	}{
+		{
+			indexType: "btree",
+			want:      "btree",
+		},
+		{
+			indexType: "hash",
+			want:      "hash",
+		},
+	}
+
+	columns := []string{}
+	for _, v := range tests {
+		columns = append(columns, fmt.Sprintf("column_%s integer", v.indexType))
+	}
+	indexes := []string{}
+	for _, v := range tests {
+		indexes = append(indexes, fmt.Sprintf("CREATE INDEX %s_index ON %s.%s USING %[1]s (column_%[1]s)", v.indexType, schema, table))
+	}
+	query := `
+		CREATE TABLE %s.%s (%s);
+		-- Indexes creation sql
+		%s
+	`
+	db.DB.Exec(fmt.Sprintf(query, schema, table, strings.Join(columns, ", "), strings.Join(indexes, ";")))
+	defer db.DB.Exec(fmt.Sprintf("DROP TABLE %s.%s", schema, table))
+
+	r := postgres.NewReader()(db.DB).(metadata.IndexReader)
+
+	t.Run("Get info about access method for specyfic index.", func(t *testing.T) {
+		accessMethods := []string{}
+		for _, v := range tests {
+			result, err := r.Indexes(metadata.Filter{Name: fmt.Sprintf("%s_index", v.indexType)})
+			if err != nil {
+				log.Fatalf("Could not get Index informatin: %s", err)
+			}
+			for result.Next() {
+				accessMethods = append(accessMethods, result.Get().Type)
+			}
+		}
+
+		for i, test := range tests {
+			if accessMethods[i] != test.want {
+				t.Errorf("Wrong %s index access method, expected:\n  %s, got:\n  %s", dbName, test.want, accessMethods[i])
+			}
+		}
+	})
+
+	t.Run("Get info about index access method for all table indexes.", func(t *testing.T) {
+		result, err := r.Indexes(metadata.Filter{Schema: schema, Parent: table})
+		if err != nil {
+			log.Fatalf("Could not get Index informatin: %s", err)
+		}
+		accessMethods := []string{}
+		for result.Next() {
+			accessMethods = append(accessMethods, result.Get().Type)
+		}
+
+		for i, test := range tests {
+			if accessMethods[i] != test.want {
+				t.Errorf("Wrong %s index access method, expected:\n  %s, got:\n  %s", dbName, test.want, accessMethods[i])
+			}
+		}
+	})
+}
diff --git a/drivers/testdata/pgsql.descTable.expected.txt b/drivers/testdata/pgsql.descTable.expected.txt
@@ -16,11 +16,11 @@
  special_features     | ARRAY                          | "YES"    |                                       |    0 |              0 |    10 |            0 
  fulltext             | tsvector                       | "NO"     |                                       |    0 |              0 |    10 |            0 
 Indexes:
-  "film_fulltext_idx" index (fulltext)
-  "film_pkey" PRIMARY_KEY, UNIQUE, index (film_id)
-  "idx_fk_language_id" index (language_id)
-  "idx_fk_original_language_id" index (original_language_id)
-  "idx_title" index (title)
+  "film_fulltext_idx" gist (fulltext)
+  "film_pkey" PRIMARY_KEY, UNIQUE, btree (film_id)
+  "idx_fk_language_id" btree (language_id)
+  "idx_fk_original_language_id" btree (original_language_id)
+  "idx_title" btree (title)
 Foreign-key constraints:
   "film_language_id_fkey" FOREIGN KEY (language_id) REFERENCES language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
   "film_original_language_id_fkey" FOREIGN KEY (original_language_id) REFERENCES language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
@@ -39,8 +39,8 @@ Triggers:
  film_id     | smallint                       | "NO"     |         |   16 |              0 |     2 |            0 
  last_update | timestamp(6) without time zone | "NO"     | now()   |    6 |              0 |    10 |            0 
 Indexes:
-  "film_actor_pkey" PRIMARY_KEY, UNIQUE, index (actor_id, film_id)
-  "idx_fk_film_id" index (film_id)
+  "film_actor_pkey" PRIMARY_KEY, UNIQUE, btree (actor_id, film_id)
+  "idx_fk_film_id" btree (film_id)
 Foreign-key constraints:
   "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
   "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
@@ -54,7 +54,7 @@ Triggers:
  category_id | smallint                       | "NO"     |         |   16 |              0 |     2 |            0 
  last_update | timestamp(6) without time zone | "NO"     | now()   |    6 |              0 |    10 |            0 
 Indexes:
-  "film_category_pkey" PRIMARY_KEY, UNIQUE, index (film_id, category_id)
+  "film_category_pkey" PRIMARY_KEY, UNIQUE, btree (film_id, category_id)
 Foreign-key constraints:
   "film_category_category_id_fkey" FOREIGN KEY (category_id) REFERENCES category(category_id) ON UPDATE CASCADE ON DELETE RESTRICT
   "film_category_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
@@ -83,23 +83,23 @@ Index "public.film_actor_pkey"
 ----------+----------
  actor_id | smallint 
  film_id  | smallint 
-primary key, index, for table film_actor
+primary key, btree, for table film_actor
 
 Index "public.film_category_pkey"
     Name     |   Type   
 -------------+----------
  film_id     | smallint 
  category_id | smallint 
-primary key, index, for table film_category
+primary key, btree, for table film_category
 
 Index "public.film_fulltext_idx"
    Name   |   Type    
 ----------+-----------
  fulltext | gtsvector 
-index, for table film
+gist, for table film
 
 Index "public.film_pkey"
   Name   |  Type   
 ---------+---------
  film_id | integer 
-primary key, index, for table film
+primary key, btree, for table film