BUG #1085: bug in the jdbc connector when doing intensive update/delete

The following bug has been logged online:

Bug reference: 1085
Logged by: Cedric

Email address: caugustin(at)alcyonis(dot)fr

PostgreSQL version: 7.4

Operating system: Red Hat Linux release 8.0

Description: bug in the jdbc connector when doing intensive
update/delete

Details:

We found a bug in the jdbc connector: when doing an intensive update/delete
on a postgres 7.3.1 or 7.4.1, the generation hangs with no computer
activity. To bypass the problem, we join 50 sql request on each database
access.

Our system :
* Red Hat Linux release 8.0 - Psyche
* PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7) (same problem with 7.3.1)
* Postgres JDBC connector jdbc2_0-stdext.jar
* Apache Tomcat/4.1.24
* Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1_02-b06)

What our program is doing : for a bus company, it reads all bus lines and
calculates all possible connections to found all possible itineraries from
any bus stop to any other bus stop. For each bus line there are different
validities. This represents around 400 000 itineraries and around 20 000
validities on our test platform.

The process hang when we calculate the validities for the itineraries.

When the generation hang, the server activity and postmaster activity are
null and the same request at the database prompt gives the exact result. The
system is fully operationnal and the database too so we supect the jdbc
connector.

When we change the database version (to 7.4.1), the generation hangs quicker
simply because Postgres is more efficient (good job, we appreciate the 30%
performance bonus).

By replacing many single request calls by a few request including many
update/delete request, the process finishes well in about 1hour.
The generation process is a thread. At first, we had about 30000
insert/update/delete requests with the same number of jdbc connections. By
grouping update/delete request by 50, we fall to less than 700 connections.

During hangup, there was no error messages or even warnings (tomcat logs or
postmaster logs).

We do not have any example to provide easily because it's a complex database
structure. If it's really necessary, we can save all the request in a file
and provide a dump.

Cedric.