Pgx: ERROR: prepared statement \"lrupsc_1_6\" already exists (SQLSTATE 42P05)

[dnguyenglu]

I'm running pgx v4 against postgres 9.6 and getting ERROR: prepared statement \"lrupsc_1_6\" already exists (SQLSTATE 42P05). Is v4 supposed to work with the extended protocol or do I still have to use the simple protocol?

[dnguyenglu]

Sorry, forgot to mention that I'm running pgbouncer 1.9.0 in between my server and db.

[jackc]

Prepared statements won't work when using pgbouncer as the underlying connection is shuffled out from under pgx by pgbouncer -- and prepared statements are used by default. You need to configure the connection to use a statement cache in "describe" mode instead of "prepare" mode -- this will use the extended protocol. You could also use the simple protocol, but the extended protocol is preferred.

See https://github.com/jackc/pgx/blob/master/pgbouncer_test.go for an example of configuring a config struct directly. You can also set it with "statement_cache_mode=describe" in your connection string.

[dnguyenglu]

Yes i saw statement_cache_mode and statement_cache_capacity docs soon after i filed this issue. Sorry for that!

[gaffneyc]

I'm seeing this same error today using transactions direct to the database without pgbouncer. The best I can figure is that it is using the same statement cache for all transactions but prepared statements are local to the transaction itself.

[gaffneyc]

Interestingly, increasing the cache size seems to fix it. We have a mix of basic interpolated queries (e.g. tx.QueryRow(ctx, fmt.Sprintf("SELECT name FROM widgets WHERE id = '%[1]s'", id))) and parameterized queries (tx.QueryRow(ctx, "SELECT name FROM widgets WHERE id = $1", id)).

I added a print statement to the cache and it looks like the basic queries are being prepared and cached.

[jackc]

I'm seeing this same error today using transactions direct to the database without pgbouncer.

Interestingly, increasing the cache size seems to fix it.

This concerned me -- so I went looking through the statement cache and found a bug when the cache overflows and then a discarded query is reissued.

Fixed in the just released v4.0.1.

[dnguyenglu]

do you know what would be an optimal size for the statement cache for an application that is serving very heavy traffic? Like > 10k transactions per second

[jackc]

The issue isn't the number of transactions per second. The issue is the number of distinct SQL statements. Presumably most of your traffic is only a handful of queries unless you are dynamically building queries.

[goku321]

Hi @jackc I have a bunch of SQL statements that would be executed multiple times concurrently with different values of the parameters. Each SQL statement is wrapped inside a function and I am using a connection pool. Here's a snippet:

func (pg *pgdb) GetUser(ctx context.Context, userID int) (*User, error) {
sqlString := "some sql query"

// Acquire a connection.
conn, err := pg.conn.Acquire(ctx)
// Prepare statement.

// Execute query here.
// Scan the row.
// Handler Error.
}

To prepare a statement, I would have to acquire a connection from the pool. Is there any other way to do it? Also, how to best use prepared statements here? I would really appreciate your help.

[jackc]

I would recommend _not_ manually preparing a statement. Rely on the automatic statement caching.

If for some reason you _really_ needed a manually prepared statement available to all the connections in your pool you could prepare it in a AfterConnect hook on the pool.

[goku321]

If I use automatic statement caching, would it also work across different connections from the pool? I mean, if a statement is cached for one connection, can a different connection reuse it?

[jackc]

if a statement is cached for one connection, can a different connection reuse it?

That is fundamentally impossible at the PostgreSQL protocol layer. Prepared statements are a connection level concept.

As far as pgx is concerned, each connection has its own statement cache. Unless you have a very unusual use case you should forget that prepared statements exist. pgx will do the right thing under the hood.

[goku321]

This is useful info. Thanks for the explanation @jackc