JDBC PreparedStatement Memory Leak.

I encountered a very large memory leak when using PreparedStatements in
a Java Application.

The memory leak only shows itself where all of the following are true:
1) The Threads which use the PreparedStatements have a long lifetime.
2) setDate() or setTimestamp is called on the PreparedStatements.
3) The PreparedStatements are being used enough to notice the leak.

In my application, my JVM was running out of memory around 330MB after
about 1 hour of
continuous opertion. I was able to find the problem by using the Java
Profiler. It showed
that around 100,000 instances of SimpleDateFormat and their subobjects
were hanging around
in memory.

It turns out that this was being caused by the way PreparedStatement was
using
ThreadLocal instances. Each one was creating a SimpleDateFormat which
via the
ThreadLocal gets set into a HashMap in the current Thread. These never
get cleaned
up until the Thread itself is GCed.

It looks like this had been discovered last summer and there were some
posts about a
patch having been committed. But the problem is still there.

Here is the patch that I made to fix this.

Cheers,
Leif

RCS file:
/projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java,v
retrieving revision 1.24
diff -w -r1.24 PreparedStatement.java
42,45c42,45
< // We use ThreadLocal for SimpleDateFormat's because they are not that
< // thread safe, so each calling thread has its own object.
< private static ThreadLocal tl_df = new ThreadLocal(); // setDate()
SimpleDateFormat
< private static ThreadLocal tl_tsdf = new ThreadLocal(); //
setTimestamp() SimpleDateFormat
---
> // Store a copy of SimpleDataFormats for each instance and synchronize
them as they
> // are not very thread safe.
> private static SimpleDateFormat df; // setDate() SimpleDateFormat
> private static SimpleDateFormat tsdf; // setTimestamp() SimpleDateFormat
351c351
< SimpleDateFormat df = (SimpleDateFormat) tl_df.get();
---
> SimpleDateFormat df = this.df;
354,355c354,356
< df = new SimpleDateFormat("''yyyy-MM-dd''");
< tl_df.set(df);
---
> // It is possible that two threads could get in here, but it
> // does not cause problems.
> this.df = df = new SimpleDateFormat("''yyyy-MM-dd''");
357c358,365
< set(parameterIndex, df.format(x));
---
>
> String formatX;
> synchronized(df)
> {
> // SimpleDateFormats are not thread safe.
> formatX = df.format(x);
> }
> set(parameterIndex, formatX);
407c415
< SimpleDateFormat df = (SimpleDateFormat) tl_tsdf.get();
---
> SimpleDateFormat df = tsdf;
409a418,419
> // It is possible that two threads could get in here, but it
> // does not cause problems.
412c422
< tl_tsdf.set(df);
---
> tsdf = df;
424a435,441
> String formatX;
> synchronized(df)
> {
> // SimpleDateFormats are not thread safe.
> formatX = df.format(x);
> }
>
429c446
<
sbuf.append("'").append(df.format(x)).append('.').append(decimal).append("+00'");
---
>
sbuf.append("'").append(formatX).append('.').append(decimal).append("+00'");