Prepared Statements stops working intermittently

[kotlinbuddy]

Describe the bug conn.Conn().Prepare statements only working 50% of the time (for simple selects with same inputs). I am getting ERROR: syntax error at or near "myQuery1" (SQLSTATE 42601) From my testing, it seems to work for 1-2 mins, then stop working for 1-2 mins, then start working for 1-2 mins, and so on. I face no issues when using the template query directly instead of prepared query's key.

To Reproduce Steps to reproduce the behavior: Not sure how to reproduce. It happens intermittently.

If possible, please provide runnable example such as:

package database

import (
    "REPO/src/constants"
    "context"
    "fmt"
    "log"

    "github.com/jackc/pgx/v5/pgxpool"
)

var db_context context.Context
var pool *pgxpool.Pool

func InitDatabase() {

    db_context = context.Background()
    DB_URL := fmt.Sprintf("postgres://%s:%s@%s:5432/%s", constants.POSTGRES_USER, constants.POSTGRES_PSWD, constants.POSTGRES_HOST, constants.POSTGRES_DB)
    pgxConn, err := pgxpool.New(db_context, DB_URL)
    if err != nil {
        log.Fatalln("Unable to connect to DB", err.Error())
    }
    pool = pgxConn

    // Prepare SQL Statements Process

    // 1. Acquire connection
    conn, err := pool.Acquire(db_context)
    defer conn.Release()
    if err != nil {
        log.Fatalln("Unable to acquire connection from pool", err.Error())
    }
    // 2. Set schema to public
    _, err = conn.Exec(db_context, `SET search_path TO public`)
    if err != nil {
        log.Fatalln("Unable to set schema to public schema", err.Error())
    }
    // 3. Send SQL statements to be prepared
    for k, v := range _QUERIES {
        _, err := conn.Conn().Prepare(db_context, k, v.query)
        if err != nil {
            log.Fatalln("Unable to prepare query", v, err.Error())
        }
    }

}

Actual behavior If instead of using the 'key', i directly use the query associated with the key, it works 100% of the time. err := pool.QueryRow(db_context, query, params...).Scan(dest...)

Expected behavior When I call QueryRow or Query using the prepared 'key', it works 50% of the time. err := pool.QueryRow(db_context, queryKey, params...).Scan(dest...) The same running process for the same inputs only works sometimes. I can't understand how can I get ERROR: syntax error at or near "myQuery1" (SQLSTATE 42601) when it just worked a minute ago for the same request.

Note: I am running a simple select statement. I am doing 1 request at a time manually (so no chance of high speed concurrency issues). The records are not being updated. You can assume the database is static (no updates being performed).

Version

• Go: $ go version -> go version go1.22.5 windows/amd64
• PostgreSQL: $ psql --no-psqlrc --tuples-only -c 'select version()' -> "PostgreSQL 16.3, compiled by Visual C++ build 1939, 64-bit"
• pgx: $ grep 'github.com/jackc/pgx/v[0-9]' go.mod -> v5.7.1

Additional context I also ran the program inside a docker container. Exactly same issue is happening with the go program compiled for alpine linux. Using golang:1.22.5-alpine3.20 for building then alpine3.20 for running. The prepared queries are working for few minutes, then stop working, then start working again. Issue doesn't happen for using the query directly

I also uninstalled and reinstalled Postgresql. I also changed the storage location from hdd to ssd. Issue still persisted.

I would understand if this might be happening due to network issue or any other issue. But I don't understand why I'm getting syntax error for idempotent queries using same input. And they stop working, then start working, then stop working, and so on. I've noticed this behavior consistently for months. I would accept it as valid syntax error if I was changing anything at all, but I literally keep everything the same.

[jackc]

You don't have a complete runnable example there, but if I had to guess I would say you are preparing the statements on individual connections and running the queries on the connection pool. When the connection pool recycles the connections your prepared statements would be lost. You might want to prepare your statements in an after connect hook on the pool instead.

But I would also suggest reconsidering the direct use of prepared statements at all. By default, pgx prepares and caches statements. Unless you are doing something unusual manual prepared statement management should be unnecessary.

[kotlinbuddy]

Ok, thanks a lot for the clarification.

I incorrectly thought Preparing statements were necessary for caching/optimizing queries. I must have misunderstood how prepared statements are supposed to work. I thought I had to call Prepare when initializing the database and pool, otherwise queries wouldn't be cached. My bad.

I don't have any unusual manual prepared statement requirement. So your suggested way works for me. Thanks a lot for helping me.