Re: Named Prepared statement problems and possible solutions

On 6/8/23 13:31, Dave Cramer wrote:
>
> On Thu, 8 Jun 2023 at 11:22, Konstantin Knizhnik <knizhnik@garret.ru
> <mailto:knizhnik@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.

Jan