Re: [ADMIN] High memory usage [PATCH]

JDBC folks, can you comment on this patch?  Thanks.


> Attached is my patch to the official 7.1.2 PreparedStatement.java class.
> This seems to work quite well for me in a test case. To try to reproduce the
> seen problem I will need to test all night. I'll report tomorrow.
>
> BTW, this is my first attempt at making a unified diff so I might have done
> something wrong. If this diff doesn't apply please tell me.
>
> --Rainer
>
> > -----Original Message-----
> > From: pgsql-admin-owner@postgresql.org
> > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Rainer Mager
> > Sent: Wednesday, June 20, 2001 9:08 AM
> > To: Tom Lane
> > Cc: PostgreSQL Admin
> > Subject: RE: [ADMIN] High memory usage
> >
> >
> > I'll work on a patch but if someone has already done this I would be
> > grateful.

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
--- PreparedStatement.java    Sat Feb 17 01:45:00 2001
+++ PreparedStatement.java.new    Wed Jun 20 12:34:57 2001
@@ -39,10 +39,12 @@
         // Some performance caches
         private StringBuffer sbuf = new StringBuffer();

-        // We use ThreadLocal for SimpleDateFormat's because they are not that
-        // thread safe, so each calling thread has its own object.
-        private ThreadLocal tl_df   = new ThreadLocal(); // setDate() SimpleDateFormat
-        private ThreadLocal tl_tsdf = new ThreadLocal(); // setTimestamp() SimpleDateFormat
+    // Because SimpleDateFormat is not thread safe we create one for each
+    // PreparedStatemnt here AND synchronize on it for each usage.
+    // We can NOT use ThreadLocal because they are not freed until the thread
+    // completes. This would be a memory leak for long running threads.
+    private SimpleDateFormat df = null;
+    private SimpleDateFormat tsdf = null;

     /**
      * Constructor for the PreparedStatement class.
@@ -90,9 +92,6 @@
          * New in 7.1 - overides Statement.close() to dispose of a few local objects
          */
         public void close() throws SQLException {
-          // free the ThreadLocal caches
-          tl_df.set(null);
-
           super.close();
         }

@@ -332,14 +331,18 @@
      */
     public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
     {
-          SimpleDateFormat df = (SimpleDateFormat) tl_df.get();
-          if(df==null) {
+        // The df DateFormat is initialized here to delay creation until needed.
+        if( df == null ) {
+            synchronized( this ) {
+                if( df == null ) {
             df = new SimpleDateFormat("''yyyy-MM-dd''");
-            tl_df.set(df);
+                }
+            }
           }

-      set(parameterIndex, df.format(x));
-
+        // We must synchronize here because SimpleDateFormat is not thread safe.
+        synchronized( df ) {
+            set( parameterIndex, df.format(x) );
       // The above is how the date should be handled.
       //
       // However, in JDK's prior to 1.1.6 (confirmed with the
@@ -351,6 +354,7 @@
       //
       //set(parameterIndex, df.format(new java.util.Date(x.getTime()+86400000)));
     }
+    }

     /**
      * Set a parameter to a java.sql.Time value.  The driver converts
@@ -375,17 +379,22 @@
      */
     public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
         {
-          SimpleDateFormat df = (SimpleDateFormat) tl_tsdf.get();
-          if(df==null) {
-            df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
-            tl_tsdf.set(df);
+        // The tsdf DateFormat is initialized here to delay creation until needed.
+        if( tsdf == null ) {
+            synchronized( this ) {
+                if( tsdf == null ) {
+                    tsdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+                    tsdf.setTimeZone(TimeZone.getTimeZone("GMT"));
+                }
+            }
           }
-          df.setTimeZone(TimeZone.getTimeZone("GMT"));

+        // We must synchronize here because SimpleDateFormat is not thread safe.
+        synchronized( tsdf ) {
           // Use the shared StringBuffer
           synchronized(sbuf) {
             sbuf.setLength(0);
-            sbuf.append("'").append(df.format(x)).append('.').append(x.getNanos()/10000000).append("+00'");
+                sbuf.append("'").append(tsdf.format(x)).append('.').append(x.getNanos()/10000000).append("+00'");
             set(parameterIndex, sbuf.toString());
           }

@@ -393,6 +402,7 @@
           // to be identical. Pays to read the docs ;-)
           //set(parameterIndex,"'"+x.toString()+"'");
     }
+    }

     /**
      * When a very large ASCII value is input to a LONGVARCHAR parameter,