What causes "cannot upsert record ${key}" in micro/go-micro?

Check the wrapped PostgreSQL error for the precise cause (connection vs constraint vs value size).

cannot upsert record ${key}

Error message

cannot upsert record ${key}

What it means

The pgx store's Write method executes an upsert (INSERT ... ON CONFLICT) for the record key, with an expiry timestamp when r.Expiry is set and NULL otherwise, and wraps any failure with 'cannot upsert record <key>'. It means the database rejected or failed the INSERT/UPSERT statement for that specific key.

Source

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

	}

	db, queries, err := s.db(options.Database, options.Table)
	if err != nil {
		return err
	}

	metadata := make(Metadata)
	for k, v := range r.Metadata {
		metadata[k] = v
	}

	if r.Expiry != 0 {
		_, err = db.Exec(s.options.Context, queries.Write, r.Key, r.Value, metadata, time.Now().Add(r.Expiry))
	} else {
		_, err = db.Exec(s.options.Context, queries.Write, r.Key, r.Value, metadata, nil)
	}
	if err != nil {
		return errors.Wrap(err, "cannot upsert record "+r.Key)
	}

	return nil
}

// Delete records with keys
func (s *sqlStore) Delete(key string, opts ...store.DeleteOption) error {
	var options store.DeleteOptions
	for _, o := range opts {
		o(&options)
	}

	db, queries, err := s.db(options.Database, options.Table)
	if err != nil {
		return err
	}

	_, err = db.Exec(s.options.Context, queries.Delete, key)

View on GitHub (pinned to 24529f1404)

Solutions

Check the wrapped PostgreSQL error for the precise cause (connection vs constraint vs value size).
Verify connectivity and that the table still exists (re-run initialization if dropped).
Retry the write on transient errors (connection reset, serialization failure).
Validate the key and value sizes against column definitions and server limits.
Confirm the expiry timestamp is representable (avoid extreme durations overflowing timestamp).

Example fix

// before
err := store.Write(&store.Record{Key: key, Value: val, Expiry: 1<<62}) // overflow-like huge expiry
// after
exp := time.Hour
if r.Expiry != 0 && r.Expiry < 24*time.Hour*365 {
	exp = r.Expiry
}
err := store.Write(&store.Record{Key: key, Value: val, Expiry: exp})

Defensive patterns

Strategy: retry

Validate before calling

// before write
if r.Key == "" || len(r.Value) > maxValueSize { return ErrInvalidRecord }
if r.Expiry < 0 || r.Expiry > maxExpiry { return ErrInvalidExpiry }
if err := db.Ping(ctx); err != nil { // reconnect pool first }

Type guard

func isWriteErr(err error) bool {
	return err != nil && strings.Contains(err.Error(), "cannot upsert record")
}

Try / catch

err := store.Write(rec)
if err != nil {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) && transientCodes[pgErr.Code] { // e.g. 08006, 40001
		return retryWithBackoff(func() error { return store.Write(rec) })
	}
	return err
}

Prevention

Retry transient PostgreSQL error codes (connection failures, serialization failures) with backoff.
Bound record expiry to values representable as timestamp with time zone.
Validate key/value sizes before writing.
Keep the pool healthy: set ConnMaxLifetime and run Ping checks.
Alert on table drops; run store initialization idempotently at boot.

When it happens

Trigger: Calling store.Write (or table.Write) with a record whose key/value/metadata cannot be written: connection failure, unique constraint conflict not covered by the ON CONFLICT clause, value too large, expired statement timeout, or invalid key characters breaking parameterization assumptions.

Common situations: Postgres connection pool exhausted or server restarted mid-write; writing records with expiry where the server clock/timezone handling mismatches; writing very large values exceeding column or packet limits; table dropped externally while the app runs.

Related errors

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