Here's our issue. Every day, we update our search path by replacing a schema with another.
So if today our search path would be public, alpha, tomorrow it will be public, beta, then back to public, alpha the day after that. We do this because we want our users to get data from the latest schema, while we do some work on the previous day's data.
Our problem is that whenever we switch the search path, we have some time to wait until the connections in Npgsql's pool are closed and get the updated search path. If you add that some user might spam our API continuously, we might end up with a connection that uses the same search path for a lot longer.
Is there a way to update the search path for the whole pool using some kind of trigger? I know that we could set a lifetime for each connection and allow for something like 30 minutes for a connection until it's closed, but I was hoping there was a better solution.
Instead of "switching the search path" (more detail is needed on what exactly that means), you can simply include the search path in the connection string, meaning that you'd be alternating between two connection strings. Since each connection string gets its own pool, there's no problem. The older pool would gradually empty thanks to connection pruning.
Otherwise, a connection pool can be emptied by calling NpsgqlConnection.ClearPool (or ClearAllPools).