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

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

1) Does pgbouncer do above for performance sake? Or pgbouncer cannot
handle extended query protocol without the mechanism?

2) In the example above I assume client A and client B connect to
pgbouncer with same user/database. Am I correct?

3) Does pgbouncer allow to use unnamed statements in extended query
protocol?

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