[2007-12-20 18:04 UTC] ghosh at q-one dot com

Would pay someone who resolves this bug. Feel free to contact me if you are interested.

 [2007-12-27 21:44 UTC] sixd@php.net

This is really an issue with temporary LOBS since getClobVal()
returns a temporary LOB.

There are two parts to the fix: changing the script and patching
the OCI8 extension.  Also don't forget to apply the patch for
http://bugs.php.net/bug.php?id=42496

Please test this suggestion and report any issues.

Thanks to Krishna & Shankar for the solution.

1. Change the test to get the results as LOBs, not as
strings. This allows the script to free temporary LOBs.

In the supplied testcase change:

    $query = "select extract(xml, '/').getclobval() from ugatest";
    $stmt = oci_parse($conn, $query);
    if (oci_execute($stmt))
        while ($result = oci_fetch_array($stmt, OCI_NUM+OCI_RETURN_LOBS))
            ;

to:

    $query = "select extract(xml, '/').getclobval() from ugatest";
    $stmt = oci_parse($conn, $query);
    if (oci_execute($stmt))
        while ($result = oci_fetch_array($stmt, OCI_NUM)) {
            // echo $result[0]->load(), "\n"; // do something with the XML
            $result[0]->free();  // free the temporary LOB
        }

The connection must be open when LOB->free() is called, as the
underlying OCILobFreeTemporary() call does a roundtrip to the
database.


2.  Patch oci8_lob.c.  The change copies some LOB freeing code
from php_oci_lob_close() into php_oci_lob_free():

--- oci8_lob.c.orig    2007-07-31 12:21:08.000000000 -0700
+++ oci8_lob.c    2007-12-27 12:33:19.000000000 -0800
@@ -647,6 +647,9 @@
  Close LOB descriptor and free associated resources */
 void php_oci_lob_free (php_oci_descriptor *descriptor TSRMLS_DC)
 {
+#ifdef HAVE_OCI8_TEMP_LOB
+    int is_temporary;
+#endif
 
     if (!descriptor || !descriptor->connection) {
         return;
@@ -662,6 +665,40 @@
         php_oci_lob_flush(descriptor, OCI_LOB_BUFFER_FREE TSRMLS_CC);
     }
 
+#ifdef HAVE_OCI8_TEMP_LOB
+    if (descriptor->type == OCI_DTYPE_LOB) {
+        PHP_OCI_CALL_RETURN(descriptor->connection->errcode,
+                OCILobIsTemporary,
+                (
+                    descriptor->connection->env,
+                    descriptor->connection->err,
+                    descriptor->descriptor,
+                    &is_temporary
+                 )
+        );
+        if (descriptor->connection->errcode != OCI_SUCCESS) {
+            php_oci_error(descriptor->connection->err, descriptor->connection->errcode TSRMLS_CC);
+            PHP_OCI_HANDLE_ERROR(descriptor->connection, descriptor->connection->errcode);
+            return 1;
+        }
+        if (is_temporary) {
+            PHP_OCI_CALL_RETURN(descriptor->connection->errcode,
+                    OCILobFreeTemporary,
+                    (
+                        descriptor->connection->svc,
+                        descriptor->connection->err,
+                        descriptor->descriptor
+                     )
+            );
+            if (descriptor->connection->errcode != OCI_SUCCESS) {
+                php_oci_error(descriptor->connection->err, descriptor->connection->errcode TSRMLS_CC);
+                PHP_OCI_HANDLE_ERROR(descriptor->connection, descriptor->connection->errcode);
+                return 1;
+            }
+        }
+    }
+#endif
+
     PHP_OCI_CALL(OCIDescriptorFree, (descriptor->descriptor, descriptor->type));
 
     zend_list_delete(descriptor->connection->rsrc_id);

 [2007-12-29 22:37 UTC] ghosh at q-one dot com

Really great! Thanks a lot!! This patch works. What I don't understand: I thought OCI_RETURN_LOBS is just a short-cut for those who don't want to write:

$s=$result[0]->load();
$result[0]->free();
$result[0]=$s;

If you use OCI_RETURN_LOBS you dont want to care about lobs but get the result as a string and forget about lobs altogether. So IMHO this should work as well. My specific problem is solved though.

 [2008-01-06 20:42 UTC] tony2001@php.net

>What I don't understand: I thought OCI_RETURN_LOBS is just a short-
>cut for those who don't want to write:

That's what I don't understand either: does the leak appear only on per-session basis or Oracle doesn't free those LOBs at all?
If the leak is only per-session, then users are not supposed even to notice it, since PHP requests are not supposed to take more than several seconds.

 [2008-01-06 23:17 UTC] ghosh at q-one dot com

Temporary LOBs are created in UGA memory. This is per-session, so 
the leak appears on a per-session basis. Nevertheless this is a 
problem, because PHP scripts dont necessarily have to run for a few 
seconds. PHP is a full-featured scripting language and can also be 
used from the command-line or to implement longer-running 
import-scripts. Even if not, the limit is quickly reached, when 
reading many rows like in my example.

 [2008-01-15 02:30 UTC] sixd@php.net

An enhanced patch was sent to the bug filer and Tony on 8th Jan.  I'm still waiting for feedback . . .

 [2008-01-15 17:30 UTC] ghosh at q-one dot com

Sorry. I have been on vacation. I will try to check the new patch 
this week if possible. Definitely next week otherwise.

 [2008-01-16 00:09 UTC] sixd@php.net

This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.


+----------------------------------------------------------
| Fixed in 5.2.6, 5.3, 6.  Queries with OCI_RETURN_LOBS will
| automatically free the DB's Temporary LOBs.  Fetches
| returning LOB locators need to use LOB->free().  See
| oci8/tests/bug43497.phpt for examples.
+-----------------------------------------------------------

 [2008-01-16 13:30 UTC] ghosh at q-one dot com

Confirmed. Fixed. OCI_RETURN_LOBS are freed automatically now. 
Thanks a lot to everyone involved!