I hit a snag today when using pgx/v5.
I'm running into an issue where my postgres queries sometimes fail due to databases that I'm hitting live behind pgbouncer.
After some investigation, I found out that pgx by default issues prepared statements to the server, which shouldn't be used with pgbouncer. Pgx itself does not recommend using the default queryexecmode if hitting databases that include pgbouncer.
The default mode they use is QueryExecModeCacheStatement.
I updated my config to lower the exec mode to QueryExecModeExec, which causes prepared statements to go away and does everything in a single round trip.
The problem with this however, is that any custom structs now have to be manually registered using pgtype.Map.RegisterDefaultPgType.
I use a number of custom structs and provide them via sqlc overrides so that my jsonb columns are well-typed in my Go code.
However, this now poses a problem because I have to figure out how to manually register these to pgx so that it doesn't reject.
Proposal
It would be great if sqlc had an option to automatically register these types that I've overridden so that I don't have to think about registering these. I can continually add new types and not forget to register them, which will definitely happen! Separately, maybe instead of an option, sqlc just automatically registers these types. Not sure if there would be a problem with that, but then it kinda covers all of the bases and the query mode becomes irrelevant.
sidwebworks
commented
1 week ago
Hey all 👋
Problem
I hit a snag today when using pgx/v5. I'm running into an issue where my postgres queries sometimes fail due to databases that I'm hitting live behind pgbouncer. After some investigation, I found out that pgx by default issues prepared statements to the server, which shouldn't be used with pgbouncer. Pgx itself does not recommend using the default queryexecmode if hitting databases that include pgbouncer.
More info here: https://pkg.go.dev/github.com/jackc/pgx/v5#QueryExecMode
The default mode they use is
QueryExecModeCacheStatement. I updated my config to lower the exec mode toQueryExecModeExec, which causes prepared statements to go away and does everything in a single round trip.The problem with this however, is that any custom structs now have to be manually registered using
pgtype.Map.RegisterDefaultPgType.I use a number of custom structs and provide them via sqlc overrides so that my jsonb columns are well-typed in my Go code. However, this now poses a problem because I have to figure out how to manually register these to pgx so that it doesn't reject.
Proposal
It would be great if sqlc had an option to automatically register these types that I've overridden so that I don't have to think about registering these. I can continually add new types and not forget to register them, which will definitely happen! Separately, maybe instead of an option, sqlc just automatically registers these types. Not sure if there would be a problem with that, but then it kinda covers all of the bases and the query mode becomes irrelevant.
Resources
What database engines need to be changed?
PostgreSQL
What programming language backends need to be changed?
Go