CREATE INDEX CONCURRENTLY in *_notx.sql must include IF NOT

Error message

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

What it means

validateMigrationExecutionMode (backend/internal/repository/migrations_runner.go:514-515) statically inspects every *_notx.sql migration before the runner executes anything. Non-transactional files are restricted to CREATE/DROP INDEX CONCURRENTLY statements, and every CREATE INDEX CONCURRENTLY must contain IF NOT EXISTS so a partially applied migration can be re-run safely (CONCURRENTLY cannot run inside a transaction, hence no atomic apply). The error fires when a normalized upper-case statement matches CREATE + INDEX + CONCURRENTLY but lacks the IF NOT EXISTS token.

Source

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

	if strings.Contains(upperContent, "BEGIN") || strings.Contains(upperContent, "COMMIT") || strings.Contains(upperContent, "ROLLBACK") {
		return false, errors.New("*_notx.sql must not contain transaction control statements (BEGIN/COMMIT/ROLLBACK)")
	}

	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) == "" {

View on GitHub (pinned to 073e92d171)

Solutions

Open the offending *_notx.sql and make the statement idempotent: CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON table(col);
Re-run the migration command; the runner re-validates the file content at startup and will now accept it.
If the statement genuinely cannot be idempotent, move it out of _notx.sql or reconsider whether it needs CONCURRENTLY at all.

Example fix

-- before (0007_index_notx.sql)
CREATE INDEX CONCURRENTLY idx_accounts_platform ON accounts(platform);

-- after
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_accounts_platform ON accounts(platform);

Defensive patterns

Strategy: validation

Validate before calling

// Run in CI / pre-deploy over the migrations dir before the app boots.
func lintNotxCreateIndexes(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 s == "" || !strings.Contains(s, "CONCURRENTLY") {
				continue
			}
			if strings.Contains(s, "CREATE") && strings.Contains(s, "INDEX") && !strings.Contains(s, "IF NOT EXISTS") {
				return fmt.Errorf("%s: CREATE INDEX CONCURRENTLY missing IF NOT EXISTS", f)
			}
	}
	return nil
}

Try / catch

// The runner already validates before executing; treat any error mentioning _notx.sql as a file-content bug, not a DB failure.
if err := migrationsRunner.Run(ctx); err != nil {
	if strings.Contains(err.Error(), "_notx.sql") {
		log.Fatalf("migration file rejected by validator: %v", err) // fix the .sql file; do not retry
	}
	return fmt.Errorf("run migrations: %w", err)
}

Prevention

Use a snippet/template for new *_notx.sql files that always includes IF NOT EXISTS on CREATE INDEX CONCURRENTLY
Add a CI step that lints migrations/*.sql with the same rules before merge
In code review, reject any _notx.sql statement lacking IF NOT EXISTS/IF EXISTS

When it happens

Trigger: A migration file whose name ends in _notx.sql contains a statement like 'CREATE INDEX CONCURRENTLY idx_groups_platform ON groups(platform);' without IF NOT EXISTS. It triggers at runner startup (app boot or migrate command), before any SQL reaches Postgres.

Common situations: Developer copies an index statement from psql history or another project that omits IF NOT EXISTS; an existing transactional migration is renamed to _notx.sql to get CONCURRENTLY support without editing the statement; habits from Postgres < 9.5 where IF NOT EXISTS on indexes did not exist.

Related errors

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