DROP INDEX CONCURRENTLY in *_notx.sql must include IF EXISTS

Error message

DROP INDEX CONCURRENTLY in *_notx.sql must include IF EXISTS for idempotency

What it means

validateMigrationExecutionMode (backend/internal/repository/migrations_runner.go:517-518) runs at migration time over each *_notx.sql file, splits it into statements, upper-cases them and strips '--' line comments. When a statement contains CONCURRENTLY and matches DROP + INDEX, it must also contain IF EXISTS, because a failed/half-finished prior deploy may have already dropped (or never created) the index and the runner re-executes the whole file outside a transaction.

Source

Thrown at backend/internal/repository/migrations_runner.go:518

	statements := splitSQLStatements(content)
	for _, stmt := range statements {
		normalizedStmt := strings.ToUpper(stripSQLLineComment(strings.TrimSpace(stmt)))
		if normalizedStmt == "" {
			continue
		}

		if strings.Contains(normalizedStmt, "CONCURRENTLY") {
			isCreateIndex := strings.Contains(normalizedStmt, "CREATE") && strings.Contains(normalizedStmt, "INDEX")
			isDropIndex := strings.Contains(normalizedStmt, "DROP") && strings.Contains(normalizedStmt, "INDEX")
			if !isCreateIndex && !isDropIndex {
				return false, errors.New("*_notx.sql currently only supports CREATE/DROP INDEX CONCURRENTLY statements")
			}
			if isCreateIndex && !strings.Contains(normalizedStmt, "IF NOT EXISTS") {
				return false, errors.New("CREATE INDEX CONCURRENTLY in *_notx.sql must include IF NOT EXISTS for idempotency")
			}
			if isDropIndex && !strings.Contains(normalizedStmt, "IF EXISTS") {
				return false, errors.New("DROP INDEX CONCURRENTLY in *_notx.sql must include IF EXISTS for idempotency")
			}
			continue
		}

		return false, errors.New("*_notx.sql must not mix non-CONCURRENTLY SQL statements")
	}

	return true, nil
}

func splitSQLStatements(content string) []string {
	parts := strings.Split(content, ";")
	out := make([]string, 0, len(parts))
	for _, part := range parts {
		if strings.TrimSpace(part) == "" {
			continue
		}
		out = append(out, part)

View on GitHub (pinned to 073e92d171)

Solutions

Change the statement to DROP INDEX CONCURRENTLY IF EXISTS idx_name; in the offending *_notx.sql file.
Re-run migrations; the validator passes once the token is present.
Keep a file/CI template for _notx migrations so both CREATE (IF NOT EXISTS) and DROP (IF EXISTS) variants always carry their guard.

Example fix

-- before (0012_drop_old_index_notx.sql)
DROP INDEX CONCURRENTLY idx_groups_legacy;

-- after
DROP INDEX CONCURRENTLY IF EXISTS idx_groups_legacy;

Defensive patterns

Strategy: validation

Validate before calling

// Pre-deploy lint: mirror the runner's DROP INDEX rule.
func lintNotxDropIndexes(files []string) error {
	for _, f := range files {
		if !strings.HasSuffix(strings.ToLower(f), "_notx.sql") {
			continue
		}
		b, err := os.ReadFile(f)
		if err != nil {
			return err
		}
		for _, stmt := range strings.Split(string(b), ";") {
			s := strings.ToUpper(strings.TrimSpace(stmt))
			if strings.Contains(s, "CONCURRENTLY") && strings.Contains(s, "DROP") && strings.Contains(s, "INDEX") && !strings.Contains(s, "IF EXISTS") {
				return fmt.Errorf("%s: DROP INDEX CONCURRENTLY missing IF EXISTS", f)
			}
	}
	return nil
}

Try / catch

// Validator error => edit the migration file; never catch-and-continue.
if err := migrationsRunner.Run(ctx); err != nil {
	if strings.Contains(err.Error(), "IF EXISTS") || strings.Contains(err.Error(), "_notx.sql") {
		log.Fatalf("fix migration file: %v", err)
	}
	return err
}

Prevention

Treat DROP INDEX CONCURRENTLY IF EXISTS as the only sanctioned form in _notx files
Keep rollback migrations in the same reviewed template as forward ones
Run the migration validator as a CI gate on every PR touching migrations/

When it happens

Trigger: A *_notx.sql migration contains 'DROP INDEX CONCURRENTLY idx_foo;' without IF EXISTS. Raised during the pre-execution validation pass, so the statement never reaches Postgres.

Common situations: Rollback/cleanup migrations written by hand without the guard; statements copied from psql sessions where the index was already dropped; replacing a plain DROP INDEX with the CONCURRENTLY variant while forgetting the idempotency token required by this runner's policy.

Related errors

AI-assisted analysis of Wei-Shaw/sub2api@073e92d171 (2026-08-15). Data as JSON: /api/errors/2d3cc0d1c9242095. Report an issue: GitHub.