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

[Dan Biagini]

On Wed, Jun 27, 2012 at 6:42 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 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.


I see, that makes sense to keep it completely transparent for general
use.  My case is for a highly specialized app, which allows for this
more aggressive behavior.  As for the pg_prepared_statements query, I
have done a quick prototype where I hacked out the DEALLOCATE call,
and am getting approximately 85% TPS increase with the strategy I
outlined above (this is on top of an initial 100% TPS increase just by
using the pooler).

Do you see any potential technical problems or pitfalls from running a
modified version of pgpool such as this?  Perhaps I should send to the
pgpool-hackers mailer too...

thanks!
Dan