<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p><br>
</p>
<div class="moz-cite-prefix">Στις 31/10/24 12:07, ο/η Tatsuo Ishii
έγραψε:<br>
</div>
<blockquote type="cite"
cite="mid:20241031.190733.879959103150685765.ishii@postgresql.org">
<blockquote type="cite">
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">In my understanding prepared statements are already supported in
Pgpool-II 4.5.
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">Allow to load balance PREPARE/EXECUTE/DEALLOCATE.
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap=""><a class="moz-txt-link-freetext" href="https://www.pgpool.net/docs/latest/en/html/release-4-5-0.html">https://www.pgpool.net/docs/latest/en/html/release-4-5-0.html</a>
Or do you mean something else?
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
I mean the prepared statements via the protocol level, not via
PREPARE/EXECUTE. I don't know the specifics about upcoming 4.6, but
I'll describe what happens now in PgBouncer 1.23.1
<a class="moz-txt-link-rfc2396E" href="https://www.pgbouncer.org/2024/08/pgbouncer-1-23-1"><https://www.pgbouncer.org/2024/08/pgbouncer-1-23-1></a> . :
<a class="moz-txt-link-freetext" href="https://www.pgbouncer.org/config.html#max_prepared_statements">https://www.pgbouncer.org/config.html#max_prepared_statements</a>
It is a mechanism to cope with prepared statements in transaction and
statement pooling mode, which in Pgpool-II still is not
implemented. Statement-pooling mode, IMHO is irrelevant to the
classical/common PostgreSQL use case, but the transaction-level
pooling mode is very useful. And in order to support
transaction-pooling with prepared statements, the pooler must keep
track of all prepared statements coming from all clients, identify
unique queries, encode them in a internal manner, and then take care
that each client that has requested a prepared statement has this
statement ready in the server, even if this a newly created server
with no knowledge of the prepared statement. We haven't tested it yet,
though.
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
Hmm, interesting. Suppose client A and client B share the same
connection pool to backend. In this case client A and client B should
not be able to use the same named statement or the named portal. How
does pgbouncer take care this case?</pre>
</blockquote>
<p>IMHO , in transaction pooling mode, if there is a backend with a
certain number of prepared statements already present those will
remain, and can only be increased by more prepared statements.
Never deallocated, until the backend exits. Example :</p>
<p>lets say client A sends prepared statement : some_prep_stmt =
'select count(*) from foo where bar=?' and</p>
<p>some_other_prep_stmt = 'select foo.* from foo where bar=?'</p>
<p>and then client B sends prepared statement :
some_prep_stmt_with_diff_name = 'select count(*) from foo where
bar=?' <br>
</p>
<p>Then pgbouncer will detect that statement some_prep_stmt and
some_prep_stmt_with_diff_name are the same statement , put them in
some cache and then both those to e.g. : <code
class="language-plaintext highlighter-rouge">PGBOUNCER_1 </code>,
while map some_other_prep_stmt to e.g. : <code
class="language-plaintext highlighter-rouge">PGBOUNCER_1</code>
.</p>
<p>Then when client A eventually binds and then executes the
statement, then pgbouncer will find a server to serve the
transaction. Regardless if it is new or old backend pgbouncer will
check to see that <code
class="language-plaintext highlighter-rouge">PGBOUNCER_1 </code>has
been prepared in this backend. If not it will prepare the
statement, then bind and execute. Lets suppose this xaction
commits.<br>
</p>
<p>Now comes client B with its : some_prep_stmt_with_diff_name .
pgbouncer parses this and detects that this statement already
exists in pgbouncer's cache as <code
class="language-plaintext highlighter-rouge">PGBOUNCER_1</code>.
Now if this client connects to the server which just served client
A, it will just bind and execute, on the exiting prepared
statement on the server. If however this is a new server, then the
prepared statement will be prepared, then bound, then executed.<br>
</p>
<p>So, basically in transaction pooling mode, no clean up whatsoever
is performed in the server. Prepared statements can only increase
in the server, in my understanding. So in transaction pooling mode
apps are not supposed to use session-based features (temp files,
etc ), hence no clean up is done by default.<br>
</p>
<blockquote type="cite"
cite="mid:20241031.190733.879959103150685765.ishii@postgresql.org">
<pre class="moz-quote-pre" wrap="">
Anyway Usama may already take them account into this transaction
pooling mode. </pre>
</blockquote>
Great!!! Thanks a lot!!<br>
<blockquote type="cite"
cite="mid:20241031.190733.879959103150685765.ishii@postgresql.org">
<pre class="moz-quote-pre" wrap="">
Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: <a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp/index_en/">http://www.sraoss.co.jp/index_en/</a>
Japanese:<a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
</pre>
</blockquote>
</body>
</html>