Repository: ariga/atlas
Pull request #715 (2022-04-17 13:43:48)

sql/postgres: fixed inspection of multicolumn indexes
I have an unique index like this:

```sql
create unique index if not exists u_entity_name on entity (
  entity_type,
  coalesce(parent_id,0),
  name
);
```

which when inspected become like this:

```hcl
index "u_entity_name" {
    unique = true
    type   = BTREE
    on {
      expr = "COALESCE(parent_id, (0)::bigint)"
    }
    on {
      expr = "COALESCE(parent_id, (0)::bigint)"
    }
    on {
      expr = "COALESCE(parent_id, (0)::bigint)"
    }
  }
```

Expected:
```hcl
index "u_entity_name" {
    unique = true
    type   = BTREE
    on {
      column = column.entity_type
    }
    on {
      expr = "COALESCE(parent_id, (0)::bigint)"
    }
    on {
      column = column.name
    }
  }
```

Reason: incorrect index metadata query.



Patch:
diff --git a/schema/schemaspec/extension.go b/schema/schemaspec/extension.go
index 61806243..35748a49 100644
--- a/schema/schemaspec/extension.go
+++ b/schema/schemaspec/extension.go
@@ -93,7 +93,7 @@ func (r *Resource) As(target interface{}) error {
 	for _, ft := range specFields(target) {
 		field := v.FieldByName(ft.Name)
 		switch {
-		case ft.isName():
+		case ft.isName() && !hasAttr(r, ft.tag):
 			if seenName {
 				return errors.New("schemaspec: extension must have only one isName field")
 			}
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 2e16fdfb..19460ff8 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -387,16 +387,16 @@ func (i *inspect) addIndexes(s *schema.Schema, rows *sql.Rows) error {
 			})
 		}
 		switch {
-		case sqlx.ValidString(expr):
-			part.X = &schema.RawExpr{
-				X: expr.String,
-			}
 		case sqlx.ValidString(column):
 			part.C, ok = t.Column(column.String)
 			if !ok {
 				return fmt.Errorf("postgres: column %q was not found for index %q", column.String, idx.Name)
 			}
 			part.C.Indexes = append(part.C.Indexes, idx)
+		case sqlx.ValidString(expr):
+			part.X = &schema.RawExpr{
+				X: expr.String,
+			}
 		default:
 			return fmt.Errorf("postgres: invalid part for index %q", idx.Name)
 		}
@@ -777,25 +777,31 @@ SELECT
 	idx.indisunique AS unique,
 	c.contype AS constraint_type,
 	pg_get_expr(idx.indpred, idx.indrelid) AS predicate,
-	pg_get_expr(idx.indexprs, idx.indrelid) AS expression,
+	pg_get_indexdef(idx.indexrelid, idx.ord, false) AS expression,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'desc') AS desc,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'nulls_first') AS nulls_first,
 	pg_index_column_has_property(idx.indexrelid, a.attnum, 'nulls_last') AS nulls_last,
 	obj_description(to_regclass($1 || i.relname)::oid) AS comment
 FROM
-	pg_index idx
+	(
+		select
+			*,
+			generate_series(1,array_length(i.indkey,1)) as ord,
+			unnest(i.indkey) AS key
+		from pg_index i
+	) idx
 	JOIN pg_class i ON i.oid = idx.indexrelid
 	JOIN pg_class t ON t.oid = idx.indrelid
 	JOIN pg_namespace n ON n.oid = t.relnamespace
 	LEFT JOIN pg_constraint c ON idx.indexrelid = c.conindid
-	LEFT JOIN pg_attribute a ON a.attrelid = idx.indexrelid
+	LEFT JOIN pg_attribute a ON (a.attrelid, a.attnum) = (idx.indrelid, idx.key)
 	JOIN pg_am am ON am.oid = i.relam
 WHERE
 	n.nspname = $1
 	AND t.relname IN (%s)
 	AND COALESCE(c.contype, '') <> 'f'
 ORDER BY
-	table_name, index_name, a.attnum
+	table_name, index_name, idx.ord
 `
 	fksQuery = `
 SELECT


Test patch:
diff --git a/schema/schemaspec/extension_test.go b/schema/schemaspec/extension_test.go
index 48385648..9f4daa8d 100644
--- a/schema/schemaspec/extension_test.go
+++ b/schema/schemaspec/extension_test.go
@@ -194,3 +194,20 @@ func TestListRef(t *testing.T) {
 	require.NoError(t, err)
 	require.EqualValues(t, resource, scan)
 }
+
+func TestNameAttr(t *testing.T) {
+	type Named struct {
+		Name string `spec:"name,name"`
+	}
+	schemaspec.Register("named", &Named{})
+	resource := &schemaspec.Resource{
+		Name: "id",
+		Attrs: []*schemaspec.Attr{
+			schemautil.StrLitAttr("name", "atlas"),
+		},
+	}
+	var tgt Named
+	err := resource.As(&tgt)
+	require.NoError(t, err)
+	require.EqualValues(t, "atlas", tgt.Name)
+}
diff --git a/schema/schemaspec/schemahcl/hcl_test.go b/schema/schemaspec/schemahcl/hcl_test.go
index 58e8067e..96ba4032 100644
--- a/schema/schemaspec/schemahcl/hcl_test.go
+++ b/schema/schemaspec/schemahcl/hcl_test.go
@@ -249,3 +249,25 @@ func TestQualified(t *testing.T) {
 	require.NoError(t, err)
 	require.EqualValues(t, h, string(out))
 }
+
+func TestNameAttr(t *testing.T) {
+	h := `
+named "block_id" {
+  name = "atlas"
+}
+ref = named.block_id.name
+`
+	type Named struct {
+		Name string `spec:"name,name"`
+	}
+	var test struct {
+		Named *Named `spec:"named"`
+		Ref   string `spec:"ref"`
+	}
+	err := Unmarshal([]byte(h), &test)
+	require.NoError(t, err)
+	require.EqualValues(t, &Named{
+		Name: "atlas",
+	}, test.Named)
+	require.EqualValues(t, "atlas", test.Ref)
+}
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 7b36d39d..b6769424 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -38,7 +38,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
- table_name  |  column_name |          data_type          | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name |  udt_name   | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype |  oid  
+ table_name  |  column_name |          data_type          | is_nullable |         column_default          | character_maximum_length | numeric_precision | datetime_precision | numeric_scale | character_set_name | collation_name |  udt_name   | is_identity | identity_start | identity_increment | identity_generation | generation_expression | comment | typtype |  oid
 -------------+--------------+-----------------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+-------------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-------
  users       |  id          | bigint                      | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8        | YES         |      100       |          1         |    BY DEFAULT       |                       |         | b       |    20
  users       |  rank        | integer                     | YES         |                                 |                          |                32 |                    |             0 |                    |                | int4        | NO          |                |                    |                     |                       | rank    | b       |    23
@@ -132,18 +132,21 @@ table_name | column_name |      data_type      | is_nullable |         column_de
 -----------+-------------+---------------------+-------------+---------------------------------+--------------------------+-------------------+--------------------+---------------+--------------------+----------------+----------+-------------+----------------+--------------------+---------------------+-----------------------+---------+---------+-------
 users      | id          | bigint              | NO          |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                     |                       |         | b       |    20
 users      | c1          | smallint            | NO          |                                 |                          |                16 |                    |             0 |                    |                | int2     | NO          |                |                    |                     |                       |         | b       |    21
+users      | parent_id   | bigint              | YES         |                                 |                          |                64 |                    |             0 |                    |                | int8     | NO          |                |                    |                     |                       |         | b       |    22
 `))
 				m.ExpectQuery(queryIndexes).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
    table_name   |    index_name   | index_type  | column_name | primary | unique | constraint_type | predicate             |   expression              | desc | nulls_first | nulls_last | comment
 ----------------+-----------------+-------------+-------------+---------+--------+-----------------+-----------------------+---------------------------+------+-------------+------------+-----------
-users           | idx             | hash        | left        | f       | f      |                 |                       | "left"((c11)::text, 100)  | t    | t           | f          | boring
-users           | idx1            | btree       | left        | f       | f      |                 | (id <> NULL::integer) | "left"((c11)::text, 100)  | t    | t           | f          |
-users           | t1_c1_key       | btree       | c1          | f       | t      | u               |                       |                           | t    | t           | f          |
-users           | t1_pkey         | btree       | id          | t       | t      | p               |                       |                           | t    | f           | f          |
-users           | idx4            | btree       | c1          | f       | t      |                 |                       |                           | f    | f           | f          |
-users           | idx4            | btree       | id          | f       | t      |                 |                       |                           | f    | f           | t          |	
+users           | idx             | hash        |             | f       | f      |                 |                       | "left"((c11)::text, 100)  | t    | t           | f          | boring
+users           | idx1            | btree       |             | f       | f      |                 | (id <> NULL::integer) | "left"((c11)::text, 100)  | t    | t           | f          |
+users           | t1_c1_key       | btree       | c1          | f       | t      | u               |                       | c1                        | t    | t           | f          |
+users           | t1_pkey         | btree       | id          | t       | t      | p               |                       | id                        | t    | f           | f          |
+users           | idx4            | btree       | c1          | f       | t      |                 |                       | c1                        | f    | f           | f          |
+users           | idx4            | btree       | id          | f       | t      |                 |                       | id                        | f    | f           | t          |
+users           | idx5            | btree       | c1          | f       | t      |                 |                       | c1                        | f    | f           | f          |
+users           | idx5            | btree       |             | f       | t      |                 |                       | coalesce(parent_id, 0)    | f    | f           | f          |
 `))
 				m.noFKs()
 				m.noChecks()
@@ -154,12 +157,14 @@ users           | idx4            | btree       | id          | f       | t
 				columns := []*schema.Column{
 					{Name: "id", Type: &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}}},
 					{Name: "c1", Type: &schema.ColumnType{Raw: "smallint", Type: &schema.IntegerType{T: "smallint"}}},
+					{Name: "parent_id", Type: &schema.ColumnType{Raw: "bigint", Null: true, Type: &schema.IntegerType{T: "bigint"}}},
 				}
 				indexes := []*schema.Index{
 					{Name: "idx", Table: t, Attrs: []schema.Attr{&IndexType{T: "hash"}, &schema.Comment{Text: "boring"}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `"left"((c11)::text, 100)`}, Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
 					{Name: "idx1", Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &IndexPredicate{P: `(id <> NULL::integer)`}}, Parts: []*schema.IndexPart{{SeqNo: 1, X: &schema.RawExpr{X: `"left"((c11)::text, 100)`}, Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
 					{Name: "t1_c1_key", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}, &ConType{T: "u"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1], Desc: true, Attrs: []schema.Attr{&IndexColumnProperty{NullsFirst: true}}}}},
 					{Name: "idx4", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1]}, {SeqNo: 2, C: columns[0], Attrs: []schema.Attr{&IndexColumnProperty{NullsLast: true}}}}},
+					{Name: "idx5", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "btree"}}, Parts: []*schema.IndexPart{{SeqNo: 1, C: columns[1]}, {SeqNo: 2, X: &schema.RawExpr{X: `coalesce(parent_id, 0)`}}}},
 				}
 				pk := &schema.Index{
 					Name:   "t1_pkey",
@@ -499,7 +504,7 @@ type mock struct {
 func (m mock) version(version string) {
 	m.ExpectQuery(sqltest.Escape(paramsQuery)).
 		WillReturnRows(sqltest.Rows(`
-  setting   
+  setting
 ------------
  en_US.utf8
  en_US.utf8