Re: Named Prepared statement problems and possible solutions - Mailing list pgsql-hackers

From Jan Wieck
Subject Re: Named Prepared statement problems and possible solutions
Date
Msg-id 98bee8f6-b8cb-e8bd-85d8-9880fde79089@wi3ck.info
Whole thread Raw
In response to Re: Named Prepared statement problems and possible solutions  (Dave Cramer <davecramer@gmail.com>)
Responses Re: Named Prepared statement problems and possible solutions
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: Dmitry Dolgov
Date:
Subject: Re: Let's make PostgreSQL multi-threaded
Next
From: Tom Lane
Date:
Subject: Re: Major pgbench synthetic SELECT workload regression, Ubuntu 23.04+PG15