Re: Statement Pooling

On Fri, May 28, 2010 at 10:09:22PM +0200, Janning wrote:
> On Thursday 27 May 2010 16:56:28 you wrote:
> > > Additionally c3p0 can cache java instances of
> > > "java.sql.PreparedStatement" which means it is caching the java object.
> > > So when using
> > > c3p0.maxStatementsPerConnection = 100 it caches at most 100 different
> > > objects. It saves time on creating objects, but this has nothing to do
> > > with the postgresql database and its prepared statements.
> > >
> > > Right?
> >
> > That's the idea.
>
> Really? Today I think c3p0 statement pooling it is not only about saving time
> in object creation, but to let postgresql reuse an already prwpeared
> statement.

Well, it was probably too strong to say it "has nothing to do with" your
database. Anyway, it sounds like you've got it right.

<snip>

> > > I remember postgresql 8.4 is replanning prepared statements when
> > > statistics change occur, but I didn't find it in the release notes. It is
> > > just saying "Invalidate cached plans when referenced schemas, functions,
> > > operators, or operator classes are modified". Does PG replans prepared
> > > statements from time to time if underlying data statistics change?
> >
> > I don't think so, though I may be wrong. The change you refer to replans
> > such things when the actual objects change, such as when you remove a
> > column or something that would make the plan fail to execute.
>
> Ok, so i should be aware of this. if the execution plan postgresql is caching
> gets old, i can run into trouble.
>
> So i should close my connections in the pool from time to time to force
> replanning of my prepared statements.

I'm not sure this is a great idea, but it would depend on your application.
With caching and pooling and such, a prepared statement might live a really
long time, but your database's statistics might take even longer to change
sufficiently to suggest one plan is better than another. Explicitly replanning
sounds like it's probably more work than it's worth, unless you see specific
plan problems.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com