is there any way to add `&binary_parameters=yes` in postgres URL/config/DSN #6936

pratikbin · 2021-04-26T06:53:54Z

pratikbin
Apr 26, 2021

Summary

I'm getting error in k8s, using Digitaloceans hosted Postgres DB with connection pool(backed by pg bouncer by Digitalocean) size 5 in transaction mode.

concourse helm chart version 15.0.2
Concourse image version 7.1.0 with below postgres config

...
        postgres:
          host: private-xxxx
          port: xxx
          sslmode: require
          connectTimeout: 5m
          database: concourse
...

error

{"timestamp":"2021-04-23T12:44:42.052226080Z","level":"error","source":"atc","message":"atc.tracker.tick.release.failed-to-release","data":{"error":"lock was lost while held, possibly due to connection breakage","id":[2,-1402787153],"session":"22.9.2"}}
{"timestamp":"2021-04-23T12:44:42.220308452Z","level":"error","source":"atc","message":"atc.collector_resource_configs.tick.acquire.failed-to-register-in-db","data":{"error":"pq: bind message supplies 2 parameters, but prepared statement \"\" requires 1","id":[2,-1875928107],"session":"32.8.1"}}
{"timestamp":"2021-04-23T12:48:22.690406550Z","level":"error","source":"atc","message":"atc.scheduler.tick.failed-to-update-last-ran","data":{"error":"pq: bind message supplies 1 parameters, but prepared statement \"\" requires 2","session":"21.30"}}
{"timestamp":"2021-04-22T08:29:57.273981100Z","level":"error","source":"atc","message":"atc.scanner.tick.acquire.failed-to-register-in-db","data":{"error":"pq: bind message supplies 2 parameters, but prepared statement \"\" requires 1","id":[2,1441783593],"session":"20.9462.1"}}

{"timestamp":"2021-04-22T08:29:57.274214976Z","level":"error","source":"atc","message":"atc.scanner.tick.failed-to-acquire-lock","data":{"error":"pq: bind message supplies 2 parameters, but prepared statement \"\" requires 1","session":"20.9462"}}
{"timestamp":"2021-04-22T08:29:57.274353259Z","level":"error","source":"atc","message":"atc.collector_workers.tick.failed-to-reload-component","data":{"error":"pq: unnamed prepared statement does not exist","session":"34.9467"}}
{"timestamp":"2021-04-22T08:29:58.582951486Z","level":"error","source":"atc","message":"atc.dex.event","data":{"fields":{},"message":"Storage health check failed: delete auth request: delete auth_request: jreaubvxc4qqe7ldq7dwtooji","session":"15"}}
{"timestamp":"2021-04-22T08:29:58.583109793Z","level":"error","source":"atc","message":"atc.collector_artifacts.tick.acquire.failed-to-register-in-db","data":{"error":"pq: unnamed prepared statement does not exist","id":[2,-72349454],"session":"26.9472.1"}}
{"timestamp":"2021-04-22T08:29:58.583143700Z","level":"error","source":"atc","message":"atc.collector_artifacts.tick.failed-to-acquire-lock","data":{"error":"pq: unnamed prepared statement does not exist","session":"26.9472"}}

it works when i remomve connection pool but it consume around 15 connections which makes problem with other applicatons because of limited connections in DB found something which can help you

https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements

Steps to reproduce

Deploy concourse helm chart version 15.0.2 with connection pool by pgbouncer size of 5 in trasnaction mode

Expected results

it should work if we didn't use without getting turbulence error in UI

Actual results

getting terbulence error in UI and below in web pod

{"timestamp":"2021-04-23T12:44:42.052226080Z","level":"error","source":"atc","message":"atc.tracker.tick.release.failed-to-release","data":{"error":"lock was lost while held, possibly due to connection breakage","id":[2,-1402787153],"session":"22.9.2"}}
{"timestamp":"2021-04-23T12:44:42.220308452Z","level":"error","source":"atc","message":"atc.collector_resource_configs.tick.acquire.failed-to-register-in-db","data":{"error":"pq: bind message supplies 2 parameters, but prepared statement \"\" requires 1","id":[2,-1875928107],"session":"32.8.1"}}
{"timestamp":"2021-04-23T12:48:22.690406550Z","level":"error","source":"atc","message":"atc.scheduler.tick.failed-to-update-last-ran","data":{"error":"pq: bind message supplies 1 parameters, but prepared statement \"\" requires 2","session":"21.30"}}
{"timestamp":"2021-04-22T08:29:57.273981100Z","level":"error","source":"atc","message":"atc.scanner.tick.acquire.failed-to-register-in-db","data":{"error":"pq: bind message supplies 2 parameters, but prepared statement \"\" requires 1","id":[2,1441783593],"session":"20.9462.1"}}

{"timestamp":"2021-04-22T08:29:57.274214976Z","level":"error","source":"atc","message":"atc.scanner.tick.failed-to-acquire-lock","data":{"error":"pq: bind message supplies 2 parameters, but prepared statement \"\" requires 1","session":"20.9462"}}
{"timestamp":"2021-04-22T08:29:57.274353259Z","level":"error","source":"atc","message":"atc.collector_workers.tick.failed-to-reload-component","data":{"error":"pq: unnamed prepared statement does not exist","session":"34.9467"}}
{"timestamp":"2021-04-22T08:29:58.582951486Z","level":"error","source":"atc","message":"atc.dex.event","data":{"fields":{},"message":"Storage health check failed: delete auth request: delete auth_request: jreaubvxc4qqe7ldq7dwtooji","session":"15"}}
{"timestamp":"2021-04-22T08:29:58.583109793Z","level":"error","source":"atc","message":"atc.collector_artifacts.tick.acquire.failed-to-register-in-db","data":{"error":"pq: unnamed prepared statement does not exist","id":[2,-72349454],"session":"26.9472.1"}}
{"timestamp":"2021-04-22T08:29:58.583143700Z","level":"error","source":"atc","message":"atc.collector_artifacts.tick.failed-to-acquire-lock","data":{"error":"pq: unnamed prepared statement does not exist","session":"26.9472"}}

Additional context

found something realted to binary_parameters here so is there any way to provide that in postgres URL of concourse

https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/

Triaging info

Concourse version: 7.1.0
Browser (if applicable): firefox 86.0 (64-bit)

Answered by jandd

Jun 17, 2024

I created concourse/flag#9 to add support to concourse/flag where the DSN for github.com/lib/pq is assembled.

View full answer

RCM7 · 2021-07-15T12:16:48Z

RCM7
Jul 15, 2021

I'm also facing this when I use pgbouncer in front of postgres. I had to use session pooling mode to avoid this problem in the meantime, which is not as efficient.

5 replies

pratikbin Jul 15, 2021
Author

man, same here

jandd Jun 14, 2024

We ran into this issue with a new PostgreSQL instance with PGBouncer too. It would be nice if we could either specify the whole DSN as a single parameter/environment variable or would have a command line option / environment variable to add additional DSN options.

jandd Jun 17, 2024

I created concourse/flag#9 to add support to concourse/flag where the DSN for github.com/lib/pq is assembled.

Answer selected by taylorsilva

taylorsilva Jun 25, 2024
Maintainer

Merged that PR and cut a new release of flags, v2.1.0: https://github.com/concourse/flag/releases/tag/v2.1.0

jandd Oct 5, 2024

the option should be available since 6c890c7 which updated the flags dependency

taylorsilva · 2025-02-18T15:55:31Z

taylorsilva
Feb 18, 2025
Maintainer

FYI: We've changed our sql driver from lib/pq to pgx: #9066

Based on recent discussion from pgx users: jackc/pgx#1784
and the recent 1.24.0 release of Pgbouncer: https://www.pgbouncer.org/2025/01/pgbouncer-1.24.0
It sounds like PgBouncer + pgx should work with no configuration changes now.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Concourse

is there any way to add `&binary_parameters=yes` in postgres URL/config/DSN #6936

Uh oh!

{{title}}

Uh oh!

Replies: 2 comments 5 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Concourse

is there any way to add &binary_parameters=yes in postgres URL/config/DSN #6936

Uh oh!

pratikbin Apr 26, 2021

Summary

Steps to reproduce

Expected results

Actual results

Additional context

Triaging info

Replies: 2 comments · 5 replies

Uh oh!

RCM7 Jul 15, 2021

Uh oh!

pratikbin Jul 15, 2021 Author

Uh oh!

jandd Jun 14, 2024

Uh oh!

jandd Jun 17, 2024

Uh oh!

taylorsilva Jun 25, 2024 Maintainer

Uh oh!

jandd Oct 5, 2024

Uh oh!

taylorsilva Feb 18, 2025 Maintainer

is there any way to add `&binary_parameters=yes` in postgres URL/config/DSN #6936

pratikbin
Apr 26, 2021

Replies: 2 comments 5 replies

RCM7
Jul 15, 2021

pratikbin Jul 15, 2021
Author

taylorsilva Jun 25, 2024
Maintainer

taylorsilva
Feb 18, 2025
Maintainer