Re: [JDBC] Support for JDBC setQueryTimeout, et al.

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> That assumption is certainly something I feel we should consider a valid
> and important use-case. I'd think a lot of the time your typical website
> is going to be using a dedicated pooler for connections and a dedicated
> database where having those queries cache'd would be good.

Using pgbouncer without setting server_reset_query is possible and allow
to reuser prepared queries. I abuse the feature in some environment
where prepare takes ~42ms and execute 5ms, as all the data is in RAM.

> I recall seeing a module that even set things up so you feed it all the
> queries that you're going to run and it plans them all out for you when
> you start up the pooler. Been meaning to look into it more, but..

Yeah, for this same project I wanted the application code to stop having
to check whether the session given already has the queries prepared. As
pgbouncer will take new connections here and there (which is a good
idea), you have to check for that. Enters preprepare:

http://preprepare.projects.postgresql.org/README.html
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/preprepare/preprepare/
http://packages.debian.org/sid/postgresql-8.4-preprepare

There's no release yet and the code is still under CVS, but I could see
about moving it to github some day. Well, maybe I also should implement
support for 9.0.

> The whole problem with search_path and role is very frustrating. We've
> taken to just hacking things to be dynamic SQL whenever it's
> role-specific, but that's a really poor solution. I wonder if it would
> be possible to have the function and prepare'd plan caches be key'd off
> of the search_path and role too..? So if you change one of those you
> end up having to re-plan it, but then that's also cached, etc..

By default pgbouncer maintains a different pool per database and role,
so you should be partly covered here.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support