The dialect for postgres get a wrong col name, it case the schema sync fail to work #1292

New Issue

lunny · 2019-05-07T03:34:23Z

yuanfan commented

2019-05-07 03:34:23 +00:00

(Migrated from github.com)

func (db *postgres) GetIndexes(tableName string) (map[string]*core.Index, error) {
	args := []interface{}{tableName}
	s := fmt.Sprintf("SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1")
	if len(db.Schema) != 0 {
		args = append(args, db.Schema)
		s = s + " AND schemaname=$2"
	}
	db.LogSQL(s, args)

	rows, err := db.DB().Query(s, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	indexes := make(map[string]*core.Index, 0)
	for rows.Next() {
		var indexType int
		var indexName, indexdef string
		var colNames []string
		err = rows.Scan(&indexName, &indexdef)
		if err != nil {
			return nil, err
		}
		indexName = strings.Trim(indexName, `" `)
		if strings.HasSuffix(indexName, "_pkey") {
			continue
		}
		if strings.HasPrefix(indexdef, "CREATE UNIQUE INDEX") {
			indexType = core.UniqueType
		} else {
			indexType = core.IndexType
		}
		cs := strings.Split(indexdef, "(")
		colNames = strings.Split(cs[1][0:len(cs[1])-1], ",")
		var isRegular bool
		if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
			newIdxName := indexName[5+len(tableName):]
			isRegular = true
			if newIdxName != "" {
				indexName = newIdxName
			}
		}

		index := &core.Index{Name: indexName, Type: indexType, Cols: make([]string, 0)}
		for _, colName := range colNames {
			index.Cols = append(index.Cols, strings.Trim(colName, `" `))
		}
		index.IsRegular = isRegular
		indexes[index.Name] = index
	}
	return indexes, nil
}

I found 2 problem atleast.

The first :

	if strings.HasSuffix(indexName, "_pkey") {
		continue
	}

the primary key indexName mybe named "primary", not always has stuffix "_pKey". I suggest add "|| strings.Contains(indexName", "primary" )" condition to handle this satuation.

The Second:

parse indexdef string to get col name, it just the string like this

CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)

but indexdef string maybe like this

CREATE UNIQUE INDEX "primary" ON xx.public.account (id ASC)
CREATE UNIQUE INDEX "UQE_account_acc_name" ON xx.public.account (acc_name ASC)
CREATE INDEX "IDX_account_acc_type" ON xx.public.account (acc_type ASC)

in this situation, the col name will be parse to "id ASC", not "id". it case schema sync get the wrong col name, then sync will be failed. I suggest to alter the code to compatible with this situation.

Normally, the dialect for postgres work good. these problems beacuse of I used the CockroachDB. they said compatible with Postgres protocol, but a little different. I have test the xorm work with CockroachDB. It work well, except schema sync. the cockroach have a lot of users, I hope the postgres dialect compatible with CockroachDB. Please handle this, like the GORM，provide the compatibility support. thinks.

```go func (db *postgres) GetIndexes(tableName string) (map[string]*core.Index, error) { args := []interface{}{tableName} s := fmt.Sprintf("SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1") if len(db.Schema) != 0 { args = append(args, db.Schema) s = s + " AND schemaname=$2" } db.LogSQL(s, args) rows, err := db.DB().Query(s, args...) if err != nil { return nil, err } defer rows.Close() indexes := make(map[string]*core.Index, 0) for rows.Next() { var indexType int var indexName, indexdef string var colNames []string err = rows.Scan(&indexName, &indexdef) if err != nil { return nil, err } indexName = strings.Trim(indexName, `" `) if strings.HasSuffix(indexName, "_pkey") { continue } if strings.HasPrefix(indexdef, "CREATE UNIQUE INDEX") { indexType = core.UniqueType } else { indexType = core.IndexType } cs := strings.Split(indexdef, "(") colNames = strings.Split(cs[1][0:len(cs[1])-1], ",") var isRegular bool if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) { newIdxName := indexName[5+len(tableName):] isRegular = true if newIdxName != "" { indexName = newIdxName } } index := &core.Index{Name: indexName, Type: indexType, Cols: make([]string, 0)} for _, colName := range colNames { index.Cols = append(index.Cols, strings.Trim(colName, `" `)) } index.IsRegular = isRegular indexes[index.Name] = index } return indexes, nil } ``` I found 2 problem atleast. The first : ```go if strings.HasSuffix(indexName, "_pkey") { continue } ``` the primary key indexName mybe named "primary", not always has stuffix "_pKey". I suggest add "|| strings.Contains(indexName", "primary" )" condition to handle this satuation. The Second: parse indexdef string to get col name, it just the string like this ```sql CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id) ``` but indexdef string maybe like this ```sql CREATE UNIQUE INDEX "primary" ON xx.public.account (id ASC) CREATE UNIQUE INDEX "UQE_account_acc_name" ON xx.public.account (acc_name ASC) CREATE INDEX "IDX_account_acc_type" ON xx.public.account (acc_type ASC) ``` in this situation, the col name will be parse to "id ASC", not "id". it case schema sync get the wrong col name, then sync will be failed. I suggest to alter the code to compatible with this situation. Normally, the dialect for postgres work good. these problems beacuse of I used the CockroachDB. they said compatible with Postgres protocol, but a little different. I have test the xorm work with CockroachDB. It work well, except schema sync. the cockroach have a lot of users, I hope the postgres dialect compatible with CockroachDB. Please handle this, like the GORM，provide the compatibility support. thinks.

lunny added the

kind

bug

label 2020-03-01 02:26:25 +00:00

lunny added this to the 1.1.1 milestone 2020-03-01 02:26:30 +00:00

lunny referenced a pull request that will close this issue

2021-06-09 01:41:51 +00:00

Compitable with cockroach #1930

lunny referenced this issue from a commit

2021-06-09 06:07:35 +00:00

Compitable with cockroach (#1930)

lunny closed this issue