On Thu, 8 Jun 2023 at 15:49, Jan Wieck <j...@wi3ck.info> wrote: > On 6/8/23 13:31, Dave Cramer wrote: > > > > On Thu, 8 Jun 2023 at 11:22, Konstantin Knizhnik <knizh...@garret.ru > > <mailto:knizh...@garret.ru>> wrote: > > > > > So it will be responsibility of client to remember text of prepared > > query to be able to resend it when statement doesn't exists at > server? > > IMHO very strange decision. Why not to handle it in connection > > pooler (doesn't matter - external or embedded)? > > > > > > I may be myopic but in the JDBC world and I assume others we have a > > `PreparedStatement` object which has the text of the query. > > The text is readily available to us. > > > > Also again from the JDBC point of view we have use un-named statements > > normally and then name them after 5 uses so we already have embedded > > logic on how to deal with PreparedStatements > > The entire problem only surfaces when using a connection pool of one > sort or another. Without one the session is persistent to the client. > > At some point I created a "functional" proof of concept for a connection > pool that did a mapping of the client side name to a pool managed server > side name. It kept track of which query was known by a server. It kept a > hashtable of poolname+username+query MD5 sums. On each prepare request > it would look up if that query is known, add a query-client reference in > another hashtable and so on. On a Bind/Exec message it would check that > the server has the query prepared and issue a P message if not. What was > missing was to keep track of no longer needed queries and deallocate them. > > As said, it was a POC. Since it was implemented in Tcl it performed > miserable, but I got it to the point of being able to pause & resume and > the whole thing did work with prepared statements on the transaction > level. So it was a full functioning POC. > > What makes this design appealing to me is that it is completely > transparent to every existing client that uses the extended query > protocol for server side prepared statements. >
Apparently this is coming in pgbouncer Support of prepared statements by knizhnik · Pull Request #845 · pgbouncer/pgbouncer (github.com) <https://github.com/pgbouncer/pgbouncer/pull/845> Dave > > > Jan > >