pq driver: prepared statement does not exist

Question

I'm trying to connect to a postresql database with the pq driver in Go. When I do it on a local copy of the database, with a connection string like

DB, err = sql.Open("postgres", "user=user password=pwd dbname=mydb sslmode=disable")

it all works well.

However, when I switch to a production server where the connection goes through pgbouncer:

DB, err = sql.Open("postgres", "user=user password=pwd host=/var/run/pgbouncer port=port dbname=mydb sslmode=disable")

I keep getting the same error for all queries, however simple:

Database error: pq: S:"ERROR" M:"prepared statement \"1\" does not exist" C:"26000" F:"prepare.c" L:"519" R:"FetchPreparedStatement"

(it's always "prepared statement \"1\"", independent of the query I'm trying to pass)

The query in both cases is run simply as follows:

res_rows, err := DB.Query(query)
if err != nil {
    log.Printf("Database error: %s\n", err)
}
for res_rows.Next() {
    ...
}

Googling suggests to turn off prepared statements, but I don't know how to do that in Go and I'm not sure it is supported at all. Any help (even a suggestion to use something else entirely) would be greatly appreciated.

You are asking us to guess how you ran the queries which generated the errors. What Query and related methods, for example, Prepare methods, did you execute to cause the error. — peterSO, Jul 12 '13 at 13:28
Sorry, updated the post. I didn't use Prepare at all, just plain DB.Query right after sql.Open. — snikolenko, Jul 12 '13 at 13:43

score 5 · Answer 1 · answered Nov 09 '18 at 11:39

5

If you're using PgBouncer you need to set binary_parameters=yes to your database dsn connection as a query parameter

try this:
DB, err = sql.Open("postgres", "user=user password=pwd dbname=mydb sslmode=disable, binary_parameters=yes")

answered Nov 09 '18 at 11:39

Logovskii Dmitrii

2,629
4
27
44

score 4 · Accepted Answer · answered Jul 12 '13 at 15:52

Package driver

type Queryer
type Queryer interface {
    Query(query string, args []Value) (Rows, error)
}
Queryer is an optional interface that may be implemented by a Conn.

If a Conn does not implement Queryer, the sql package's DB.Query will first prepare a query, execute the statement, and then close the statement.

I don't see where the lib/pq PostgreSQL driver implements Queryer. Therefore, the DB.Query query is prepared before execution.

PgBouncer doesn't support the PREPARE feature for all pooling methods: Feature matrix for pooling modes.

Thank you for the explanation! So basically this means that I cannot use Go in this setup? — snikolenko, Jul 12 '13 at 20:51

score 4 · Answer 3 · answered Jan 25 '16 at 09:54

4

The Postgres driver now has a solution to fix this issue: https://github.com/lib/pq/issues/389

It isn't in the documentation but works as expected, including with PgBouncer and Transaction pooling enabled.

answered Jan 25 '16 at 09:54

Antoine F

86
1
4

pq driver: prepared statement does not exist

3 Answers3