[Zope] Leaking Oracle connections & processes

[Shai Berger]

Hi guys,

We are trying to build a site with Zope and Oracle, and we see again
and again a situation where Oracle connections and processes are
being leaked: the more we work, the more there are, until we reach
Oracle's limit for number of processes. At this point, the Connection
objects close on their own, and you can't even open SQL*Plus until
Zope is closed or restarted.

We use Oracle's default limit on the number of processes, which is 50.
We have about three users (developers), two Oracle connections in the
application (one for SqlSessions and one for other data), and although
we use some devious multithreading (some of our transactions call an
external method which essentially URLRetrieve()s a URL from our own
Zope server in a separate thread), 50 open connections should be more 
than enough. I have checked a little before the disaster, and it seems
that not all those processes actually have connections; the problem
may be unrelated to open connections, but just to processes.

We do one fishy thing that may also be related: We keep some long raw
data; we keep it all in one table, and other tables which need long
columns hold pointers (indices) into this table. To put data into this
table from Zope, we use an external method SaveLONG, which uses the 
database connection to access the DB (in order to use the 
transactionality of the whole thing). We use it in ZSQL methods as 
follows:

####################################################################
INSERT INTO items (
field, 
long_field_ptr
) VALUES (
<dtml-sqlvar value type=string>
<dtml-var "SaveLONG(DB_CONNECTION, long_value)">
)
####################################################################

SaveLONG saves the long value in the long data table and returns
its index in the table.
The source for SaveLONG is this. It relies on a DB trigger to
populate the LONG_ID field from a sequence.

####################################################################
long_table_name = 'LONG_DATA'
long_column_name = 'LONG_DATA'
long_seq_name = 'SEQ_LONG_DATA'
index_column_name = 'LONG_ID'

save_command = '''
INSERT INTO %s (%s)
VALUES (:content)
'''                    % (long_table_name,long_column_name)

get_key_command = 'SELECT %s.currval FROM dual' % long_seq_name

#
# Put a long raw on the DB
#
def SaveLONG(self,connection, content):
    connection = connection._v_database_connection.db # the real DB
connection object
    connection.execute(save_command, content=dbi.dbiRaw(content))
    connection.execute(get_key_command)
    return connection.fetchone()[0]
####################################################################

We're using Zope 2.2.4 with ZOracleDA 2.1.0 and DCOracle 1.3.2
with Oracle 8.0.5 on an intel-linux (RH).

Can anyone see anything wrong with what we're doing? Is anyone else
experiencing this kind of problems?

Thanks in advance,
	Shai