Describe - alternative to Prepare for PgBouncer and 1 round trip queries

[jackc]

We can prepare the unnamed prepared statement to get a description of the query text. That description could be used to send a query with the extended protocol in a single round trip. This could improve performance in some cases and it would should the extended protocol usable with PgBouncer even in statement mode.

[james-lawrence]

any postgresql documentation for what you're describing, I'm curious and interested. =)

[jackc]

https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY -- in particular the unnamed prepared statement and the describe message. The PostgreSQL protocol supports one round trip extended query just fine. The trick is that with pgx we want to use the binary format when possible. But we need to do a describe to know the types of the response values to know if we can interpret the binary format so when not using a prepared statement it typically takes 2 round trips. But we also can't use prepared statements with PgBouncer. So that leaves falling back to the simple protocol.

Currently a normal non-prepared query is executed by pgx as follows:

Step 1:

Parse
Describe
Sync (I would have expected Flush should work here -- but I ran into some problems when I tried).

Step 2:

Execute
Sync

PgBouncer is free to send step 1 and 2 to different backends because of the Sync. If that happens it fails.

I'm picturing something like this would solve it:

Step 1:

Parse
Describe
Sync

Step 2:

Parse
Execute
Sync

We end up Parseing twice, but it should make the extended protocol compatible with PgBouncer. And if we save the results of the Describe like a pseudo client side prepared statement then we can reuse the described statement for single round trip queries as well.

[jackc]

I've implemented described statement caching (#559). This makes it trivial to use PgBouncer with the extended protocol.

[JohnnyQQQQ]

@jackc could you elaborate if there is anything one needs to configure in order to use it with PgBouncer in transaction pool mode?

[jackc]

Connect with statement_cache_mode=describe.

https://godoc.org/github.com/jackc/pgx#ParseConfig

[dahu33]

@jackc would you consider statement_cache_mode=describe safe in case of schema changes (e.g. a database migration)?

[jackc]

No, it is not.

[dahu33]

Thanks for the precision. So to recap for anyone looking for this information. If you have an app that need to support live schema changes (e.g. a database migration):

• statement_cache_mode=describe is not a safe option.
• statement_cache_mode=prepare is a safe option. On a call to a cached statement that was affected by a schema change, pgx will invalidate the cached statement on the first call but will return an error without retrying (see issue connection local statement cache does not correctly invalidate #841 and the associated PR fix stmtcache invalidation #865).
• statement_cache_capacity=0 is probably the only safe option that do not involve pgx retuning an error to the application in case of schema change.