Philip Yarra wrote:
> I always assumed what Dave just said, but porting from Oracle & Sybase
> to PostgreSQL, we ran into exactly the same issue - we also solved it
> with savepoints. However, I threw together the attached sample app to
> test *precisely* what ends up in the database when auto-commit is off.
> For the impatient, it sets auto-commit off, and tries to insert 3 rows.
> The first succeeds, the second violates a unique index, so fails, and
> the third is issued after the second, so should also fail. We ignore the
> exceptions, then commit. The results puzzle me somewhat:
> static void executeInsert(int id, String msg)
> {
> log("executeInsert, id[" + id + "] msg[" + msg + "]");
> PreparedStatement stmt = null;
> try{
> stmt = conn.prepareStatement("INSERT INTO tempextest(id,msg) VALUES(?,?)");
> stmt.setInt(1,id);
> stmt.setString(2,msg);
> stmt.executeUpdate();
> conn.commit();
> } catch (SQLException sqlex) {
> log(sqlex);
> closeStatement(stmt);
> }
> }
Um, your testcase is committing after every insert, not once at the end.
So the behaviour you see sounds correct.
-O