Thread: BUG #1085: bug in the jdbc connector when doing intensive update/delete
BUG #1085: bug in the jdbc connector when doing intensive update/delete
From
"PostgreSQL Bugs List"
Date:
The following bug has been logged online: Bug reference: 1085 Logged by: Cedric Email address: caugustin@alcyonis.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.