What causes "cannot create expiry index" in micro/go-micro?

Grant the DB user permission to create indexes on the schema/database.

cannot create expiry index

Error message

cannot create expiry index

What it means

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.

Source

Thrown at store/postgres/pgx/pgx.go:106

	return dbObj.conn, dbObj.tables[table], nil
}

func (s *sqlStore) initTable(database, table string) error {
	db := s.databases[database].conn

	_, err := db.Exec(s.options.Context, fmt.Sprintf(createTable, database, table))
	if err != nil {
		return errors.Wrap(err, "cannot create table")
	}

	_, err = db.Exec(s.options.Context, fmt.Sprintf(createMDIndex, table, database, table))
	if err != nil {
		return errors.Wrap(err, "cannot create metadata index")
	}

	_, err = db.Exec(s.options.Context, fmt.Sprintf(createExpiryIndex, table, database, table))
	if err != nil {
		return errors.Wrap(err, "cannot create expiry index")
	}

	s.databases[database].tables[table] = NewQueries(database, table)

	return nil
}

func (s *sqlStore) initDB(database string) error {
	if len(s.options.Nodes) == 0 {
		s.options.Nodes = []string{"postgresql://root@localhost:26257?sslmode=disable"}
	}

	source := s.options.Nodes[0]
	// check if it is a standard connection string eg: host=%s port=%d user=%s password=%s dbname=%s sslmode=disable
	// if err is nil which means it would be a URL like postgre://xxxx?yy=zz
	_, err := url.Parse(source)
	if err != nil {
		if !strings.Contains(source, " ") {

View on GitHub (pinned to 24529f1404)

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.

Example fix

-- before
-- CREATE INDEX ... USING btree (expiry) fails: permission denied
-- after
ALTER TABLE micro.keys OWNER TO micro_user;
-- or
GRANT CREATE ON SCHEMA public TO micro_user;

Defensive patterns

Strategy: validation

Validate before calling

// before init
var canIdx bool
conn.QueryRow(ctx, "SELECT has_schema_privilege(current_user, $1, 'CREATE')", schemaName).Scan(&canIdx)
if !canIdx { // grant or pre-create the expiry index }

Try / catch

if err := store.Init(); err != nil {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		switch pgErr.Code {
		case "42501": // privileges — fix grants, retry
		case "40P01": // deadlock — retry with backoff
		}
	}
	return err
}

Prevention

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.

When it happens

Trigger: 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.

Common situations: 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.

Related errors

AI-assisted analysis of micro/go-micro@24529f1404 (2026-09-01). Data as JSON: /api/errors/23fc5782246cee1b. Report an issue: GitHub.