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

[Achilleas Mantzios]

Στις 31/10/24 12:07, ο/η Tatsuo Ishii έγραψε:
>>> In my understanding prepared statements are already supported in
>>> Pgpool-II 4.5.
>>>> Allow to load balance PREPARE/EXECUTE/DEALLOCATE.
>>> https://www.pgpool.net/docs/latest/en/html/release-4-5-0.html
>>>
>>> Or do you mean something else?
>> 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
>> <https://www.pgbouncer.org/2024/08/pgbouncer-1-23-1>  . :
>> https://www.pgbouncer.org/config.html#max_prepared_statements
>>
>> 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.
> 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?

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.

>
> Anyway Usama may already take them account into this transaction
> pooling mode.
Great!!! Thanks a lot!!
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English:http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20241031/ab0b8ec6/attachment-0001.htm>