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
Attachment:
signature.asc
Description: Digital signature
