{"record":{"id":"c176479f8924dc62","repo":"micro/go-micro","slug":"cannot-upsert-record-key","errorCode":null,"errorMessage":"cannot upsert record ${key}","messagePattern":"cannot upsert record (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"store/postgres/pgx/pgx.go","lineNumber":349,"sourceCode":"\t}\n\n\tdb, queries, err := s.db(options.Database, options.Table)\n\tif err != nil {\n\t\treturn err\n\t}\n\n\tmetadata := make(Metadata)\n\tfor k, v := range r.Metadata {\n\t\tmetadata[k] = v\n\t}\n\n\tif r.Expiry != 0 {\n\t\t_, err = db.Exec(s.options.Context, queries.Write, r.Key, r.Value, metadata, time.Now().Add(r.Expiry))\n\t} else {\n\t\t_, err = db.Exec(s.options.Context, queries.Write, r.Key, r.Value, metadata, nil)\n\t}\n\tif err != nil {\n\t\treturn errors.Wrap(err, \"cannot upsert record \"+r.Key)\n\t}\n\n\treturn nil\n}\n\n// Delete records with keys\nfunc (s *sqlStore) Delete(key string, opts ...store.DeleteOption) error {\n\tvar options store.DeleteOptions\n\tfor _, o := range opts {\n\t\to(&options)\n\t}\n\n\tdb, queries, err := s.db(options.Database, options.Table)\n\tif err != nil {\n\t\treturn err\n\t}\n\n\t_, err = db.Exec(s.options.Context, queries.Delete, key)","sourceCodeStart":331,"sourceCodeEnd":367,"githubUrl":"https://github.com/micro/go-micro/blob/24529f140421a11a33b6999ab7944f2021cfd69c/store/postgres/pgx/pgx.go#L331-L367","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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)."],"exampleFix":"// before\nerr := store.Write(&store.Record{Key: key, Value: val, Expiry: 1<<62}) // overflow-like huge expiry\n// after\nexp := time.Hour\nif r.Expiry != 0 && r.Expiry < 24*time.Hour*365 {\n\texp = r.Expiry\n}\nerr := store.Write(&store.Record{Key: key, Value: val, Expiry: exp})","handlingStrategy":"retry","validationCode":"// before write\nif r.Key == \"\" || len(r.Value) > maxValueSize { return ErrInvalidRecord }\nif r.Expiry < 0 || r.Expiry > maxExpiry { return ErrInvalidExpiry }\nif err := db.Ping(ctx); err != nil { // reconnect pool first }","typeGuard":"func isWriteErr(err error) bool {\n\treturn err != nil && strings.Contains(err.Error(), \"cannot upsert record\")\n}","tryCatchPattern":"err := store.Write(rec)\nif err != nil {\n\tvar pgErr *pgconn.PgError\n\tif errors.As(err, &pgErr) && transientCodes[pgErr.Code] { // e.g. 08006, 40001\n\t\treturn retryWithBackoff(func() error { return store.Write(rec) })\n\t}\n\treturn err\n}","preventionTips":["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."],"tags":["postgres","sql","upsert","write"],"backgroundTag":"sql-write-failed","analyzedSha":"24529f140421a11a33b6999ab7944f2021cfd69c","analyzedAt":"2026-09-01T02:52:24.923Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T05:18:18.240Z"}