Re: why would an update only work if done twice? - Mailing list pgsql-jdbc

Caoilte,

One thing that would be useful here is the logs from the postgres
backend to see what postgres is actually seeing. Is it possible to get a
look at the logs. I run code like yours in my applications with no
problems

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of C O'Connor
Sent: September 28, 2001 9:41 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] why would an update only work if done twice?


I know this is a mistake I have made, and I am pretty sure it is a
mistake that has been occuring ever since I caught an SQL exception
(tried to insert value with already existing primary key).

At a guess I'd say that the db didn't recover properly because I didn't
rollback when i caught that exception.

THE PROBLEM
When I do an insert/update on the db using my jdbc code it goes through
correcty and does NOT raise any errors. However an examination of the db
using psql reveals that the table hasn't been updated at all. This is
scary, as it seems I cannot guarantee that any data has ever been
committed (I DO CALL COMMIT).

Furthermore if while my tomcat instance is still running (I have the
struts pooled connection running on top of it) I try and modify that
table in the database from within psql it hangs. It will only complete
when tomcat is stopped (presumably meaning that tomcat is hanging
somewhere despite not throwing an exception or hanging itself).

The SCARY thing is that I can fix this problem, with this code :-


      conn = dataSource.getConnection();
      if (auform.getOldUserName().equals("") | auform.getOldUserName()
== null) {
        if (adminUser.getAdminAccountAccess()) {
          System.out.println("inserting admin user");
          auuBean.insertAdminUser(conn, saveAdminUser);
        } else
          errors = true;
      } else {
        System.out.println("updating admin user");
        auuBean.updateAdminUser(conn, saveAdminUser,
auform.getOldUserName());
      }
      conn.commit();
      conn.close();

// CODE INSERTED TWICE!!!!!!!!!!!!
      conn = dataSource.getConnection();
      if (auform.getOldUserName().equals("") | auform.getOldUserName()
== null) {
        if (adminUser.getAdminAccountAccess()) {
          System.out.println("inserting admin user");
          auuBean.insertAdminUser(conn, saveAdminUser);
        } else
          errors = true;
      } else {
        System.out.println("updating admin user");
        auuBean.updateAdminUser(conn, saveAdminUser,
auform.getOldUserName());
      }
      conn.commit();
      conn.close();
// END CONFUSION

      conn = null;
    } catch (SQLException e) {
      while (e != null) {
        System.out.println("First Error Message: " + e.getMessage());
        e = e.getNextException();
      }
      if (conn != null) {
        try {
          conn.rollback();
        } catch (SQLException f) {
          System.out.println("heavy shit rolling back db transaction");
        }
      }
      errors = true;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          System.out.println("heavy shit losing a db pool connection");
          errors = true;
        }
        conn = null;
      }
    }

A separate utility bean actually does the update or insert and throws
the exception for the action servlet to catch here.

My fix is the extra update/insert. For some reason this will update the
database whereas NOTHING else will. I have tried vacuuming, reindexing
and they don't seem to change anything.

I am running the latest version sid version of postgresql 7.1.3-4, and
connecting with a struts 1.0 pooled connection through tomcat. The code
above has worked before, but stopped working after it caught an SQL
exception (and before I put the rollback code in).

Can anyone suggest,
a) what the problem could be?
b) how i could fix it?
c) how to make sure it never happens again?

Caoilte


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly