{"record":{"id":"23fc5782246cee1b","repo":"micro/go-micro","slug":"cannot-create-expiry-index","errorCode":null,"errorMessage":"cannot create expiry index","messagePattern":"cannot create expiry index","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"store/postgres/pgx/pgx.go","lineNumber":106,"sourceCode":"\treturn dbObj.conn, dbObj.tables[table], nil\n}\n\nfunc (s *sqlStore) initTable(database, table string) error {\n\tdb := s.databases[database].conn\n\n\t_, err := db.Exec(s.options.Context, fmt.Sprintf(createTable, database, table))\n\tif err != nil {\n\t\treturn errors.Wrap(err, \"cannot create table\")\n\t}\n\n\t_, err = db.Exec(s.options.Context, fmt.Sprintf(createMDIndex, table, database, table))\n\tif err != nil {\n\t\treturn errors.Wrap(err, \"cannot create metadata index\")\n\t}\n\n\t_, err = db.Exec(s.options.Context, fmt.Sprintf(createExpiryIndex, table, database, table))\n\tif err != nil {\n\t\treturn errors.Wrap(err, \"cannot create expiry index\")\n\t}\n\n\ts.databases[database].tables[table] = NewQueries(database, table)\n\n\treturn nil\n}\n\nfunc (s *sqlStore) initDB(database string) error {\n\tif len(s.options.Nodes) == 0 {\n\t\ts.options.Nodes = []string{\"postgresql://root@localhost:26257?sslmode=disable\"}\n\t}\n\n\tsource := s.options.Nodes[0]\n\t// check if it is a standard connection string eg: host=%s port=%d user=%s password=%s dbname=%s sslmode=disable\n\t// if err is nil which means it would be a URL like postgre://xxxx?yy=zz\n\t_, err := url.Parse(source)\n\tif err != nil {\n\t\tif !strings.Contains(source, \" \") {","sourceCodeStart":88,"sourceCodeEnd":124,"githubUrl":"https://github.com/micro/go-micro/blob/24529f140421a11a33b6999ab7944f2021cfd69c/store/postgres/pgx/pgx.go#L88-L124","documentation":"initTable next executes a CREATE INDEX statement for the expiry column and wraps failures with 'cannot create expiry index'. This index supports TTL-based record expiry queries; without it the table is created but time-based cleanup queries will be inefficient or the initialization aborts.","triggerScenarios":"First access to an uninitialized table where the CREATE INDEX on the expiry timestamp column fails: privilege errors, invalid identifiers interpolated into the DDL, connection drop, or lock contention with concurrent initializers.","commonSituations":"Restricted DB role on managed Postgres; wrong database/table naming in store configuration producing invalid identifiers; concurrent app instances initializing the same table simultaneously; network interruption mid-initialization.","solutions":["Grant the DB user permission to create indexes on the schema/database.","Inspect the wrapped cause for the exact PostgreSQL error message.","Retry after resolving lock contention if another instance was initializing.","Pre-create the expiry index manually matching the store's DDL.","Verify the configured database/table names form valid identifiers."],"exampleFix":"-- before\n-- CREATE INDEX ... USING btree (expiry) fails: permission denied\n-- after\nALTER TABLE micro.keys OWNER TO micro_user;\n-- or\nGRANT CREATE ON SCHEMA public TO micro_user;","handlingStrategy":"validation","validationCode":"// before init\nvar canIdx bool\nconn.QueryRow(ctx, \"SELECT has_schema_privilege(current_user, $1, 'CREATE')\", schemaName).Scan(&canIdx)\nif !canIdx { // grant or pre-create the expiry index }","typeGuard":null,"tryCatchPattern":"if err := store.Init(); err != nil {\n\tvar pgErr *pgconn.PgError\n\tif errors.As(err, &pgErr) {\n\t\tswitch pgErr.Code {\n\t\tcase \"42501\": // privileges — fix grants, retry\n\t\tcase \"40P01\": // deadlock — retry with backoff\n\t\t}\n\t}\n\treturn err\n}","preventionTips":["Grant index-creation privileges or pre-create the expiry index via migrations.","Use an advisory lock so only one instance runs initTable at a time.","Keep database/table identifiers simple (alphanumeric/underscore).","Retry initialization on transient lock/deadlock errors.","Capture the wrapped PostgreSQL error code on failure."],"tags":["postgres","sql","index","ttl"],"backgroundTag":"sql-index-creation-failed","analyzedSha":"24529f140421a11a33b6999ab7944f2021cfd69c","analyzedAt":"2026-09-01T02:52:24.923Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T05:18:18.240Z"}