[pgpool-general: 668] Re: pgpool 3.1.2 - reusing named prepared statements

[Tatsuo Ishii]

> I am evaluating postgres connection poolers (pgbouncer and pgpool),
> specifically I’m looking for a way to allow reusing prepared
> statements on the backend connections across all frontend client
> connections.
> 
> The use-case is for a handful of complex but performance critical
> queries which get executed by short lived client processes.  So
> ideally client app code would connect to pooler, and check if the
> prepared statement already exists for this connection (via
> pg_prepared_statement table).  If it doesn’t the client will create
> the statement(s), and execute them, then disconnect, but leaving the
> prepared statement allocated on the backend for use by a subsequent
> client connection.
> 
> It doesn’t seem to work with pgpool (I found pgpool was calling
> DEALLOCATE in the connection cleanup code, regardless of whether it
> was configured in the reset_query_list configuration).
> 
> Is this a known behavior?

Yes. Pgpool is designed to be as much transpaent as possible to
clients. That says, if client disconnects to pgpool, all per
PostgreSQL backend process resources including prepared statments
should disappear.

BTW, everytime you use a prepared stament, you are going to check
pg_prepared_statements? I'm not sure if it's cheaper than the cost to
parese a query.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp