Postgres, pq, pgbouncer, and prepared statements

This is an informational post mean to help reduce debugging time for future users.

I’m using:

  • Temporal 1.17.1
  • postgres 10
  • pgbouncer 1.11.0

Upon using temporal-sql-tool and temporal-server, I was seeing error messages in stdout and logging reporting: pq: unnamed prepared statement does not exist.

Searches revealed this error is apparently caused by pgbouncer being upset over the use of plaintext parameters to prepared statements. The solution is to instruct pq to use binary parameters.

temporal-sql-tool

set SQL_CONNECT_ATTRIBUTES="binary_parameters=yes" or supply --connect-attributes "binary_parameters=yes"

config-*.yml

        connectAttributes:
          # required to allow pq driver to make prepared statements via pgbouncer
          binary_parameters: "yes"

So far this parameter seems to be well-tolerated by the various temporal services.

See also: PgBouncer and prepared statements | Developer's life

2 Likes

Temporal 1.20.0 apparently struggles with pgbouncer in transaction pooling mode.

I see this error message coming from temporal-history: pq: unsupported jsonb version number 123

I opened a ticket, so hopefully we’ll see this addressed: temporal-history: pq: unsupported jsonb version number 123 · Issue #4184 · temporalio/temporal · GitHub