Fix PostgreSQL array parsing for index columns with special characters

[sgaunet]

Problem

The PostgreSQL array parsing in internal/app/client.go (lines 336-340) uses simple string manipulation that doesn't properly handle escaped commas or quoted array elements.

Current implementation:

columnsStr := row["columns"].(string)
columnsStr = strings.Trim(columnsStr, "{}")
if columnsStr != "" {
    index.Columns = strings.Split(columnsStr, ",")
}

Issues

This approach will fail on:

1. Column names with commas:

   CREATE INDEX idx ON table ("col,with,commas", normal_col)
   -- Array: {col,with,commas,normal_col}
   -- Parsed incorrectly as 4 columns instead of 2

2. Quoted identifiers:

   CREATE INDEX idx ON table ("Column Name", "another")
   -- Array: {"Column Name","another"}
   -- May parse incorrectly depending on PostgreSQL output format

3. Special characters in column names:

   CREATE INDEX idx ON table ("col{value}", "col}data")
   -- Array may contain nested braces

PostgreSQL Array Format

PostgreSQL arrays are returned in formats like:

{column1,column2,column3}
{"Column Name","another column"}
{col1,"col,with,comma",col3}

Proposed Solutions

Option 1: Use PostgreSQL Array Type (Recommended)

Query the array as a proper PostgreSQL array type instead of text:

query := `
    SELECT 
        indexname,
        indexdef,
        array_agg(attname ORDER BY attnum) as columns  -- Returns proper array type
    FROM pg_indexes
    JOIN pg_class ON ...
    GROUP BY indexname, indexdef
`

// Then scan directly into []string
var columns pq.StringArray
err := rows.Scan(&index.Name, &index.Definition, &columns)
index.Columns = []string(columns)

Option 2: Use lib/pq Array Parser

Use the pq package's built-in array parsing:

import "github.com/lib/pq"

columnsStr := row["columns"].(string)
var columns pq.StringArray
if err := columns.Scan([]byte(columnsStr)); err != nil {
    logger.Log().Warn().Err(err).Msg("Failed to parse column array")
    continue
}
index.Columns = []string(columns)

Option 3: Proper String Parsing

Implement proper PostgreSQL array parsing respecting quotes and escapes:

func parsePostgreSQLArray(arrayStr string) []string {
    // Trim outer braces
    arrayStr = strings.Trim(arrayStr, "{}")
    if arrayStr == "" {
        return []string{}
    }
    
    var result []string
    var current strings.Builder
    inQuotes := false
    escaped := false
    
    for _, ch := range arrayStr {
        if escaped {
            current.WriteRune(ch)
            escaped = false
            continue
        }
        
        switch ch {
        case '\\':
            escaped = true
        case '"':
            inQuotes = !inQuotes
        case ',':
            if !inQuotes {
                result = append(result, current.String())
                current.Reset()
                continue
            }
            current.WriteRune(ch)
        default:
            current.WriteRune(ch)
        }
    }
    
    if current.Len() > 0 {
        result = append(result, current.String())
    }
    
    return result
}

Recommended Approach

Use Option 1 or Option 2 (lib/pq array types) as they're most reliable and leverage the PostgreSQL driver's built-in functionality.

Impact

• Severity: LOW-MEDIUM
• Type: Bug Fix
• Location: internal/app/client.go:336-340
• Risk: Breaks on edge cases with special characters

Checklist

• Update query to return proper PostgreSQL array type or use pq.StringArray
• Replace string manipulation with proper array parsing
• Add test cases for column names with commas
• Add test cases for quoted identifiers
• Add test cases for special characters
• Verify existing indexes are parsed correctly after changes