Hey there. I've had this nasty bug with CNPG and Temporal 1.30.1
Expected Behavior
When using the postgres12_pgx SQL plugin, inserts into current_executions should succeed, as they do with the postgres12 plugin.
Actual Behavior
All pods are Running and schema migration jobs Completed successfully — no crashes, no restarts. However, the worker continuously fails to create system workflows with:
ERROR: invalid input syntax for type integer: "Created" (SQLSTATE 22P02)
Full log:
{"level":"error","ts":"...","msg":"Operation failed with internal error.",
"error":"createOrUpdateCurrentExecution failed. Failed to insert into current_executions table. Error: ERROR: invalid input syntax for type integer: \"Created\" (SQLSTATE 22P02)",
"operation":"CreateWorkflowExecution"}The current_executions.state column is INTEGER NOT NULL (schema v1.18), but the string "Created" is being sent instead of 0.
Switching to postgres12 plugin with connectAttributes.binary_parameters: "yes" resolves the issue — identical setup otherwise.
Steps to Reproduce the Problem
- Configure Temporal Server 1.30.1 with
postgres12_pgx plugin, PostgreSQL 12+
- Start the server — all pods come up healthy
- Check worker logs —
CreateWorkflowExecution fails on current_executions INSERT
- Switch to
postgres12 plugin with binary_parameters: yes — issue disappears
Specifications
- Version: 1.30.1
- Platform: Linux (Kubernetes), PostgreSQL 18 via CNPG
- Plugin:
postgres12_pgx
- PgBouncer in transaction pooling mode
Workaround
Use postgres12 plugin with connectAttributes.binary_parameters: "yes" for PgBouncer compatibility, as per https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/
Possible root cause analysis (speculative, by Claude — may contain inaccuracies):
CurrentExecutionsRow.State is typed as enumsspb.WorkflowExecutionState, a protobuf-generated type based on int32 that implements fmt.Stringer with String() returning "Created", "Running", etc. We suspect pgx encodes this via text formatting (fmt.Sprint() → String()) instead of using the underlying int32 value, while lib/pq resolves the underlying integer via reflection. This would affect any write path passing protobuf enums to integer columns.
Hey there. I've had this nasty bug with CNPG and Temporal 1.30.1
Expected Behavior
When using the
postgres12_pgxSQL plugin, inserts intocurrent_executionsshould succeed, as they do with thepostgres12plugin.Actual Behavior
All pods are
Runningand schema migration jobsCompletedsuccessfully — no crashes, no restarts. However, the worker continuously fails to create system workflows with:Full log:
{"level":"error","ts":"...","msg":"Operation failed with internal error.", "error":"createOrUpdateCurrentExecution failed. Failed to insert into current_executions table. Error: ERROR: invalid input syntax for type integer: \"Created\" (SQLSTATE 22P02)", "operation":"CreateWorkflowExecution"}The
current_executions.statecolumn isINTEGER NOT NULL(schema v1.18), but the string"Created"is being sent instead of0.Switching to
postgres12plugin withconnectAttributes.binary_parameters: "yes"resolves the issue — identical setup otherwise.Steps to Reproduce the Problem
postgres12_pgxplugin, PostgreSQL 12+CreateWorkflowExecutionfails oncurrent_executionsINSERTpostgres12plugin withbinary_parameters: yes— issue disappearsSpecifications
postgres12_pgxWorkaround
Use
postgres12plugin withconnectAttributes.binary_parameters: "yes"for PgBouncer compatibility, as per https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/Possible root cause analysis (speculative, by Claude — may contain inaccuracies):
CurrentExecutionsRow.Stateis typed asenumsspb.WorkflowExecutionState, a protobuf-generated type based onint32that implementsfmt.StringerwithString()returning"Created","Running", etc. We suspect pgx encodes this via text formatting (fmt.Sprint()→String()) instead of using the underlyingint32value, while lib/pq resolves the underlying integer via reflection. This would affect any write path passing protobuf enums to integer columns.