PgBouncer problems with Go

In the last two months I have been working on a small project in Go. To describe it in as few words as possible it is a database cached service. It proxies some services on the Internet and if you call it with the same parameters you get the same result as before, but from the underlying database. Using Go for this task has been a win from the start. I was given a previous version of this service that didn’t have the database caching part, written in an ancient version of Tornado and to add database caching to this looked like a mission impossible because Tornado runs everything as coroutines so database writing would block. I decided to switch technology because Go looked more adept to the needed use-case. After implementing more than half of it a colleague of mine pointed me to an async library (Momoko) for PostgreSQL but it just felt unnatural with a bunch of yield statements every few lines of code.

I used pq and PostgreSQL 9.4.5 for the tests and it worked great. The next step was to put PgBouncer (1.7.2) in front of the database to better use the available resources. The service used 10 DB workers, but after measuring the real usage, only 2-3 connections to the database sufficed. I used the default configuration for PgBouncer and when the service started I got a really strange error.

pq: Unsupported startup parameter: extra_float_digits

I was sure I didn’t specify this parameter in the connection string. After digging a little bit I found it is a parameter of PgBouncer.

; Comma-separated list of parameters to ignore when given
; in startup packet.  Newer JDBC versions require the
; extra_float_digits here.
;
;ignore_startup_parameters = extra_float_digits

It seems that the pq library behaves similarly to the newer JDBC versions. After uncommenting this parameter, the error was gone.

I didn’t expect any more problems, but I was wrong. As the code calls some stored procedures I was very surprised to see errors like this:

pq: invalid input syntax for type double precision: "not_double_value"

After checking all the code twice I was sure that the parameters in the queries are correct. And they work correctly when you connect directly to the database. So what really happens? It turns out that sometimes when you call the stored procedure like this:

dbErr := db.QueryRow("SELECT * FROM stored_procedure($1, $2, $3);",
    param1, param2, param3)

It really gets called like this:

dbErr := db.QueryRow("SELECT * FROM stored_procedure($1, $2, $3);",
    param2, param3, some_random_value)

The diver shifts the parameters and you get the error mentioned before if the shifted arguments do not fit. In my case, you find shifted data in your tables and loose one day trying to figure out what kind of cosmic radiation can cause this. Google can be your friend, even on the 20th page :) So, there is some undocumented parameter of the pq driver that can be turned on and make this stuff work correctly. This parameter is called binary_parameters, and it can be provided in the connection string like this:

postgres://user:pass@host/dbname?sslmode=disable&binary_parameters=yes

And now it works like expected.

The same problem can be manifested with an error regarding unnamed prepared statements:

ERROR: unnamed prepared statement does not exist

Probably the some similar shifting problem caused that too.