configuring schema using search path does not work well in pgbouncer in transaction mode

[elee1766]

so I can't seem to find a way to use poll only mode with a custom search path behind pgbouncer/similar in transaction pooling mode.

the recommendation I have read is to use pgx "search_path" in order to configure the schema that the river clients will use.

but behind pgbouncer, search path is not supported, for technical reasons.

https://github.com/pgbouncer/pgbouncer/issues/89

we use an in-house rewrite of pgbouncer and so we made search path work correctly for session connections, which seems to be working, but tracking search path in transaction mode is a bigger lift so we haven't tried (as the search path isn't in parameters, you would need to do an extra query before giving the conn the session for the transaction to set the search path)

for now we are just going to run everything in session mode.

[brandur]

We're backed into a bit of a corner on this one.

Postgres offers little in the way of methods for selecting a schema. The two ways are search_path or prefixing relations explicitly like SELECT * FROM schema.relation. The fact that PgBouncer doesn't support search_path means that it doesn't support exactly 50% of the mechanisms for using a schema, which is pretty bad.

We've thought about prefixing everything like schema.relation, but we're on sqlc in the backend, and because Postgres doesn't allow parameterization in a query there (can't do SELECT $1.relation), sqlc doesn't support it either.

I know Blake's thinking about following up with sqlc to see if possibly some solution can be found there, but IMO the real answer is search_path and always has been, and this should be considered a PgBouncer bug.

[elee1766]

i was afraid this would be the case, as i was reading into a long standing sqlc issue during research

session mode is fine for the foreseeable future, but hopefully this can be resolved one day through some means.

[bgentry]

Can you link me to the specific sqlc issue you found? Want to make sure I can link to the appropriate ones when I reach out to them about it.

[elee1766]

Can you link me to the specific sqlc issue you found? Want to make sure I can link to the appropriate ones when I reach out to them about it.

I was reading through this

https://github.com/sqlc-dev/sqlc/discussions/2635

[bgentry]

@elee1766 might want to tune in to https://github.com/sqlc-dev/sqlc/pull/3370 🤞🏻

[elee1766]

we ran into session exhaustion in production.

we've resolved it by reducing the amount of sessions each insert only client is given.

this would really help, since even at 2 sessions per insert only client, we have more than 100 processes so it's still a good amount of sessions that are so close to being txn mode.