Batch insertion problem

[Mario Cageggi]

Hi folks!

I try to execute such an insert batch with the following code:
PreparedStatement pStmt = this.connection.prepareStatement("INSERT INTO person_contact_queue " +
" ( x, y, z, k, w ) " +
" VALUES (?,?,?,?,?);");
try{
this.connection.setAutoCommit(false);
for (PersonContactQueue person:people){

pStmt.setLong(1,x1);
pStmt.setLong(2, y1);
pStmt.setString(3, z1);
pStmt.setString(4, k1);
pStmt.setLong(5, w1);

pStmt.addBatch();
}
pStmt.executeBatch();

this.connection.commit();

}catch(Exception e){
e.printStackTrace();
this.connection.rollback();
}

Well, when I execute this code in my PC (connected to the same db) everything run smoothly;
when I execute it in the production environment, the insertion is done correctly, but at the following db access I get this error:

org.postgresql.util.PSQLException: ERROR: VACUUM cannot run inside a transaction block

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at com.ecglobalpanel.dao.PersonContactQueueDAO.executeVacuum(PersonContactQueueDAO.java:29)
at com.ecglobalpanel.controller.ReserveSampleBatch.doGet(ReserveSampleBatch.java:108)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) (etc...)

or:

org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:233)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at com.ecglobalpanel.dao.ProjectTypeDAO.getProjectType(ProjectTypeDAO.java:44)
at com.ecglobalpanel.controller.GetProject.doGet(GetProject.java:46)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) (etc...)

As far as I can read, it looks like I do not succeed to end the transaction block in the production server... I have no idea how to do it...

What do you think?
Please HEEEELPPPPP!!!

Cheers,
Mario

[Jan Cumps]

What happens if you restore the autocommit setting after commit / rollback?
You changed the behavior of your connection in this piece of code, and did not revert it in it's original state. Maybe your other code expects that autocommit is true?

(and if possible, please prepare your statement in your try block)

Regards, Jan

[Mario Cageggi]

I am testing right that... I will let you know!
and thanks anyway!

Mario

[Samuel March]

Could you have been firing that as "not a Transaction" by setting in your development machine so it was transparent to seeing it as a transaction during the test?

[Mario Cageggi]

I restored the autocommit true after the batch insertion and now it looks like it is working fine!

Thanks you all a lot guys! Always helpful!
Mario