Function getIndexes

backend/plugin/db/redshift/sync.go:554–647

getIndexes gets all indices of a database.

(txn *sql.Tx)

// getIndexes gets all indices of a database.
func getIndexes(txn *sql.Tx) (map[db.TableKey][]*storepb.IndexMetadata, error) {
	indexMap := make(map[db.TableKey][]*storepb.IndexMetadata)

	// Use generate_series to mimic PostgreSQL's generate_subscripts for getting column expressions
	// Combined with pg_get_indexdef to get accurate column expressions
	query := `
	WITH index_positions AS (
		SELECT generate_series(0, 31) as pos
	)
	SELECT 
		pgidx.schemaname,
		pgidx.tablename, 
		pgidx.indexname,
		pgidx.indexdef,
		ix.indisunique,
		ix.indisprimary,
		p.pos + 1 as position,
		pg_get_indexdef(pc.oid, p.pos + 1, true) as column_expression,
		obj_description(pc.oid) AS comment
	FROM
		pg_indexes AS pgidx 
		JOIN pg_namespace AS pns ON pns.nspname = pgidx.schemaname
		JOIN pg_class AS pc ON pc.relname = pgidx.indexname AND pns.oid = pc.relnamespace
		JOIN pg_index AS ix ON ix.indexrelid = pc.oid
		CROSS JOIN index_positions p
	WHERE 
		pgidx.schemaname NOT IN ('pg_catalog', 'information_schema')
		AND ix.indkey[p.pos] > 0  -- Only process positions that have actual columns
	ORDER BY
		pgidx.schemaname, pgidx.tablename, pgidx.indexname, p.pos;`

	rows, err := txn.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// Track index data as we process rows
	type indexKey struct {
		schema string
		table  string
		index  string
	}
	indexData := make(map[indexKey]*storepb.IndexMetadata)
	indexColumns := make(map[indexKey][]string)

	for rows.Next() {
		var schemaName, tableName, indexName, indexDef string
		var isUnique, isPrimary bool
		var position int
		var columnExpression sql.NullString
		var comment sql.NullString

		if err := rows.Scan(&schemaName, &tableName, &indexName, &indexDef,
			&isUnique, &isPrimary, &position, &columnExpression, &comment); err != nil {
			return nil, err
		}