[Zope] Oracle or PostGreSQL connection pool

[Nemeth Miklos]

Hannu Krosing wrote:

> Michel Pelletier wrote:
> >
> > > -----Original Message-----
> > > From: Nemeth Miklos [mailto:nemeth@iqsoft.hu]
> > >
> > >
> > > Do the Oracle or PostgreSQL database adapters and the ZSQL technology
> > > support connection pooling?
> >
> > Oracle Yes, PostgreSQL is not written by DC so I don't know.  I suspect
> > yes.
> >
> > > If I create a connection object, supposedly it may only be used by a
> > > single thread.
> >
> > Not on Oracle.  The Oracle DA is fully concurrent.  Postgres, I don't
> > know.  Possible not.  Note that if a DA is not concurrent it's not
> > Zope's fault, it's usually because the client libraries to interface
> > that database are not concurrent.
>
> I guess that what he meant is _not_ concurrency per se, but whetaher you
> can have multiple _connections_ with different transactions for each running
> thread.

If a DA supports "multiple _connections_ with different transactions for each
running
thread" as you perfectly described, this is enough for me, and I'd regard this
a concurrent behaviour.

>
> So when I define a DA and I have 3 working threads running concurrently,
> will Zope automatically get a separate _connection_ for each thread,
> either form a pool or pre-allocated per thread ?

I created a test application, which assures this concurrent behaviour of
ZOracleDA.
I defined a lengthy Oracle operation called Sleep:

create or replace procedure Sleep(iDuration in number) is
  st number;
  dif number;
begin
  st := dbms_utility.get_time;
  dif := 0;
  while dif < iDuration loop
    dif := dbms_utility.get_time - st;
  end loop;
end;

and then I invoked it with different arguments from concurrent clients (ie
browser windows).
Everything worked fine!
The only problem I found was, when I started several lengthy operations (with
iDuration of 6000 (60 sec)) and cancelled the requests by pressing the
browser's stop button.
Zope did not like this test, some requests intended to be short (ie iDuration =
100) returned sometimes after 20 seconds. But Zope did not crashed.
And what's more: ZOracleDA autometically commited transactions for successful
requests, and rolled back on exceptions. Did you know that! Amazing!
On the whole, I am content with ZOracleDA, it should be improved (better stored
proc support, bind variable support), but at the moment it can be used in a
production environment.
The only thing I am a bit afraid of is the stability of ZOracleDA in real
production environments. The most terrible thing that may happen with a
developer is when his tools behaved OK during initial benchmarks and
development, and just before sending the application into  production it starts
breaking.
I would be glad of hearing some success story with ZOracleDA.

NM