[pgpool-general: 9260] Re: Question about the global pool

[Achilleas Mantzios]

Στις 1/11/24 02:02, ο/η Tatsuo Ishii έγραψε:

>> 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 :
>>
>> lets say client A sends prepared statement : some_prep_stmt = 'select
>> count(*) from foo where bar=?' and
>>
>> some_other_prep_stmt = 'select foo.* from foo where bar=?'
>>
>> and then client B sends prepared statement :
>> some_prep_stmt_with_diff_name = 'select count(*) from foo where bar=?'
>>
>> 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. : |PGBOUNCER_1 |, while map
>> some_other_prep_stmt to e.g. : |PGBOUNCER_1| .
>>
>> 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
>> |PGBOUNCER_1 |has been prepared in this backend. If not it will
>> prepare the statement, then bind and execute. Lets suppose this
>> xaction commits.
>>
>> 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 |PGBOUNCER_1|. 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.
>>
>> 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.
> Thank you for the detailed explanation! May I ask you additional
> questions?
Good day dear Tatsuo
>
> 1) Does pgbouncer do above for performance sake? Or pgbouncer cannot
> handle extended query protocol without the mechanism?

In my understanding,  this is for performance, but also mandatory in 
transaction-pooling.  It examines queries and it identifies identical 
prepared statements even if they come from different clients. Therefore 
it maximizes the chances for a prepared statement to be already present 
(pre-prepared) in a server.

Without the mechanism, in session-pooling mode there would be no 
problem. But in transaction-pooling mode, there would clearly be a 
problem since a prepared statement might be called across many 
transactions by the same client, so no one would be sure if the next 
random server for the next transaction in the same client has the 
prepared statement or not. This is evident in past pgbouncer versions 
(without the prepared statements support), when ppl used to get ERRORs 
because of this scenario (prepared statement does not exist, or smth 
like that). Hence pgbouncer used to ask JDBC ppl to set 
prepareThreshold=0 in their data source configs. Easy to reproduce, in 
pgbouncer we set max_prepared_statements = 0, but in JDBC/app we setup 
for prepared statements usage (prepareThreshold=1), after  the 2nd 
invocation we'll get the ERROR.

> 2) In the example above I assume client A and client B connect to
> pgbouncer with same user/database. Am I correct?
Yes of course, in order for Client A and B to share the same set of 
servers they have to connect to the same pool, so same user/database.
>
> 3) Does pgbouncer allow to use unnamed statements in extended query
> protocol?

Yes, this is classic usage, prior to prepared statements support, when 
every prepared statement basically had to be unnamed.

e.g. If in JDBC we set prepareThreshold=5 (default), then the JDBC will 
send unnamed stmts in the first 5 invocations of a certain query, and 
convert to named after that. It works correctly either way, nowadays.

>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp