Problems with composite unique indexes in CockroachDB

[huth-pesser]

Description

When you create a unique index in CockroachDB you can find the columns in the query

SELECT c.column_name, constraint_type FROM information_schema.table_constraints tc JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name WHERE constraint_type IN ('PRIMARY KEY', 'UNIQUE') AND c.table_catalog = ? AND c.table_schema = ? AND c.table_name = ?

If you do the same with Postgres, in this query the columns of unique indexes are not returned.

When you define a composite unique indexes with CockroachDB, Gorm incorrectly creates individual unique indexes for each column, because of the behaviour of CockroachDB described above.

I have come up with the following workaround for the problem, but I don't know if this is the best solution to get around the problem:

is (migrator.go, line 437)

// check primary, unique field
		{
			columnTypeRows, err := m.DB.Raw("SELECT c.column_name, constraint_type FROM information_schema.table_constraints tc JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name WHERE constraint_type IN ('PRIMARY KEY', 'UNIQUE') AND c.table_catalog = ? AND c.table_schema = ? AND c.table_name = ?", currentDatabase, currentSchema, table).Rows()
			if err != nil {
				return err
			}

			for columnTypeRows.Next() {
				var name, columnType string
				columnTypeRows.Scan(&name, &columnType)
				for _, c := range columnTypes {
					mc := c.(*migrator.ColumnType)
					if mc.NameValue.String == name {
						switch columnType {
						case "PRIMARY KEY":
							mc.PrimaryKeyValue = sql.NullBool{Bool: true, Valid: true}
						case "UNIQUE":
							mc.UniqueValue = sql.NullBool{Bool: true, Valid: true}
						}
						break
					}
				}
			}
			columnTypeRows.Close()
		}

my solution:

// check primary, unique field
		{
			columnTypeRows, err := m.DB.Raw("SELECT constraint_name FROM information_schema.table_constraints tc JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name WHERE constraint_type IN ('PRIMARY KEY', 'UNIQUE') AND c.table_catalog = ? AND c.table_schema = ? AND c.table_name = ? AND constraint_type = ?", currentDatabase, currentSchema, table, "UNIQUE").Rows()
			if err != nil {
				return err
			}
			uniqueContraints := map[string]int{}
			for columnTypeRows.Next() {
				var constraintName string
				columnTypeRows.Scan(&constraintName)
				uniqueContraints[constraintName]++
			}
			columnTypeRows.Close()

			columnTypeRows, err = m.DB.Raw("SELECT c.column_name, constraint_name, constraint_type FROM information_schema.table_constraints tc JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name WHERE constraint_type IN ('PRIMARY KEY', 'UNIQUE') AND c.table_catalog = ? AND c.table_schema = ? AND c.table_name = ?", currentDatabase, currentSchema, table).Rows()
			if err != nil {
				return err
			}
			for columnTypeRows.Next() {
				var name, constraintName, columnType string
				columnTypeRows.Scan(&name, &constraintName, &columnType)
				for _, c := range columnTypes {
					mc := c.(*migrator.ColumnType)
					if mc.NameValue.String == name {
						switch columnType {
						case "PRIMARY KEY":
							mc.PrimaryKeyValue = sql.NullBool{Bool: true, Valid: true}
						case "UNIQUE":
							if uniqueContraints[constraintName] == 1 {
								mc.UniqueValue = sql.NullBool{Bool: true, Valid: true}
							}
						}
						break
					}
				}
			}
			columnTypeRows.Close()
		}

I query all elements that have constraint_type = 'UNIQUE' and count the elements per constraint_name. Unique constraints are only set if the constraint name has a count of one. Composite unique indices have a count of 2 or more and are ignored in the lower part of the routine, which solves the problem with the wrong unique indexes.

If you want I can create a pull request for this change.

[rwrz]

Since we are experiencing the same issue here, I took the pro-activity to create the PR with proposed solution. It works on CockroachDB, but I haven't tested it on any PostgreSQL version.