Inserts disappear after some time under high load - Mailing list pgsql-jdbc

Hello,
We run a high load site in which teachers create and assign Quizzes that
students take. On an average we have about 150 to 200 students taking
tests concurrently not to forget teachers using our reports feature to
generate reports.

We have explored all other possibilities and are exploring the possibility
that the JDBC driver might be at fault for the problem we are facing. Any
help/suggestions are welcome.

The problem is with table inserts not visible after some time. We
confirmed that the inserts went in earlier by logging the primary key that
we got from the DB. After some time the record itself disappears. The
problem occurs irrespective of whether we do things in a transaction or
not. So we don't set autocommit false or do any rollback either. We also
don't get any exceptions for the first insert not going through. Moreover,
we have noticed that this happens ONLY under high load. Our setup is as
follows.
Postgresql 7.3.4
pg73jdbc3 driver
Redhat 9.0
Apache/Tomcat 3.3.1
We don't use any connection pooling.

Problem description:
There are 2 tables in the DB . One which stores quiz session information
like ipaddress,date_taken etc (quiz_session) and the other which stores
student responses (quiz_responses) .

============      ============
quiz_session      quiz_responses
===========       ============
session_id(PK)    quiz_responses_id(PK)
date_taken        session_id(FK)
etc..             responses ...etc


When the student starts the quiz we create a entry in the quiz_session
table by first selecting the nextval from the quiz_session_session_id
sequence(session_id being the primary key). We store this session_id in a
variable(temp_session_id) and do an insert into quiz_session for that
student. This statement never throws an exception and the value returned
by the execution of this query is never less than 1. We print out the
session_id we received to a log file.

We store the session_id we got in the first step in the Tomcat session in
an object for later use when the students atart answering questions one by
one.

When the student answers a question we try to insert the same session_id
to the quiz_responses table as a foreign key along with his responses. The
constraints are already set up. This proceeds normally under normal load
but throws a referential integrity exception under high load. We also log
this insert into the other table to a file and found out that the
session_id which we tried to insert is the same one that we got in the
first step. Moreover when we go back and check the quiz_session table for
the record having this session id, the record itself is not found and that
can mean that the first insert didn't go through at all. But what baffles
us is why we didn't get SQL exception at that point.


We want to find out whether the semantics of the transaction are affected
by high load and whether the DB driver can be a possible reason for this
kind of behaviour..

Any help is highly appreciated.
Thanks,
Rajiv