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

[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?

Anyway Usama may already take them account into this transaction
pooling mode. 

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