3 ms·
That would be very difficult to do, Postgres prepared statements are attached to connections so prepared statements would require connection pinning of some sor
by sitharus 4y ago
That would be very difficult to do, Postgres prepared statements are attached to connections so prepared statements would require connection pinning of some sort.
It would be nice to have a wider scope for prepared statements.
- phamilton 4y agoI understand why it's difficult, but most in-process connection poolers will manage prepared statements for you even as the connection pool grows and shrinks. I know it's more complicated, but I imagine pgbouncer could detect "prepare foo as..." and "execute foo(...)" and track whether a given session has had "foo" prepared. If not, rerun the "prepare foo as ..." on a session if "execute foo(...)" is received. That's essentially what the in-process version does. There would be some complications around conflicts for a given named prepared statement, but that could be solved with some requirements on clients (e.g. use source hash in prepared statement names).
- chasers 4y agoI’m thinking can just use :erlang.phash2 to pin a client connection process to a db connection process. The only thing is a slow query could block a db connection for others pinned to it.
- phamilton 4y agoInteresting approach, but that would be messy when multiple instances of an application all try to prepare the same query with the same name on the same server connection. Clients would need to know that an "prepared statement with that name already exists" error is ok and they should go ahead and execute it. Should we take this discussion into a GitHub issue?
- chasers 3y agoFor sure > https://github.com/supabase/supavisor/issues/69 https://github.com/supabase/supavisor/issues/69