{"record":{"id":"efe314cadfd66b71","repo":"juanfont/headscale","slug":"dropping-index-w","errorCode":null,"errorMessage":"dropping index: %w","messagePattern":"dropping index: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"critical","filePath":"hscontrol/db/db.go","lineNumber":561,"sourceCode":"\t\t\t},\n\t\t\t{\n\t\t\t\tID: \"202511122344-remove-newline-index\",\n\t\t\t\tMigrate: func(tx *gorm.DB) error {\n\t\t\t\t\t// Reformat multi-line indexes to single-line for consistency\n\t\t\t\t\t// This migration drops and recreates the three user identity indexes\n\t\t\t\t\t// to match the single-line format expected by schema validation\n\n\t\t\t\t\t// Drop existing multi-line indexes\n\t\t\t\t\tdropIndexes := []string{\n\t\t\t\t\t\t`DROP INDEX IF EXISTS idx_provider_identifier`,\n\t\t\t\t\t\t`DROP INDEX IF EXISTS idx_name_provider_identifier`,\n\t\t\t\t\t\t`DROP INDEX IF EXISTS idx_name_no_provider_identifier`,\n\t\t\t\t\t}\n\n\t\t\t\t\tfor _, dropSQL := range dropIndexes {\n\t\t\t\t\t\terr := tx.Exec(dropSQL).Error\n\t\t\t\t\t\tif err != nil {\n\t\t\t\t\t\t\treturn fmt.Errorf(\"dropping index: %w\", err)\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\n\t\t\t\t\t// Recreate indexes in single-line format\n\t\t\t\t\tcreateIndexes := []string{\n\t\t\t\t\t\t`CREATE UNIQUE INDEX idx_provider_identifier ON users(provider_identifier) WHERE provider_identifier IS NOT NULL`,\n\t\t\t\t\t\t`CREATE UNIQUE INDEX idx_name_provider_identifier ON users(name, provider_identifier)`,\n\t\t\t\t\t\t`CREATE UNIQUE INDEX idx_name_no_provider_identifier ON users(name) WHERE provider_identifier IS NULL`,\n\t\t\t\t\t}\n\n\t\t\t\t\tfor _, createSQL := range createIndexes {\n\t\t\t\t\t\terr := tx.Exec(createSQL).Error\n\t\t\t\t\t\tif err != nil {\n\t\t\t\t\t\t\treturn fmt.Errorf(\"creating index: %w\", err)\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\n\t\t\t\t\treturn nil","sourceCodeStart":543,"sourceCodeEnd":579,"githubUrl":"https://github.com/juanfont/headscale/blob/565fd254d06c4c7f9a8cad1714a43445c79ba420/hscontrol/db/db.go#L543-L579","documentation":"Migration '202511122344-remove-newline-index' fails while dropping one of the users-table identity indexes (idx_provider_identifier, idx_name_provider_identifier, idx_name_no_provider_identifier). DROP INDEX IF EXISTS only guards against a missing index; failure indicates missing DROP privilege, lock contention, or (on PostgreSQL) a dependent constraint referencing the index.","triggerScenarios":"Startup migration on a database whose users indexes were created multi-line by an older headscale; the DROP fails because another session holds a blocking transaction on users, or the role lacks DROP on the schema, or a UNIQUE constraint depends on the index on PostgreSQL.","commonSituations":"Upgrading headscale while long-lived admin sessions or pgbouncer pooling keep transactions open on the users table; least-privilege DB roles missing DDL rights; Postgres refusing to drop an index that backs a UNIQUE constraint.","solutions":["Read the wrapped error to distinguish 'permission denied' (grant DROP/DDL) from lock timeout (close idle transactions)","Restart headscale during a maintenance window when no other clients are connected to the database","On PostgreSQL, if the index backs a UNIQUE constraint, drop the constraint instead (ALTER TABLE users DROP CONSTRAINT ...) after taking a backup, then let the migration recreate the index","Re-verify after startup that the three indexes exist in single-line form"],"exampleFix":null,"handlingStrategy":"try-catch","validationCode":"// Ensure no long-open transactions block DDL before upgrading (Postgres)\nrows, _ := db.Query(`SELECT pid, state, xact_start FROM pg_stat_activity\n\tWHERE state <> 'idle' AND xact_start < now() - interval '1 minute'`)\n// terminate or wait; idle-in-transaction sessions cause the DROP to hang or fail","typeGuard":null,"tryCatchPattern":"// When wrapping headscale startup: catch, surface the wrapped cause, exit non-zero\nif err := db.NewHeadscaleDatabase(cfg); err != nil {\n\tif strings.Contains(err.Error(), \"dropping index:\") {\n\t\tlog.Error().Err(err).Msg(\"index migration failed; check locks/privileges, then retry startup\")\n\t}\n\tos.Exit(1)\n}","preventionTips":["Terminate idle-in-transaction sessions before upgrading","Avoid pgbouncer transaction-pooling in front of migration runs","Give the role DROP on the schema"],"tags":["database","migration","index","postgres","locking"],"backgroundTag":null,"analyzedSha":"565fd254d06c4c7f9a8cad1714a43445c79ba420","analyzedAt":"2026-08-15T13:12:30.133Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}