Here is the scenario I would like to achieve. My project is a spring boot project with postgreql database.
There is a huge data set, where I need to use PostgreSQL CopyManager copyIn(). However I may have some SQL commands, which must be run before the copyIn and some to run after the CopyIn. I want all commands running in one transaction, meaning if any SQL command is failed, all changes should be rolled back.
However, it seems transaction is not working! The command before the copyIn() is a truncate table command, there is an issue in Copy TO command (so, in copyIn()), in catch I rollback, but still the truncate table is not rolled back. the table becomes empty.
PgConnection pgConnection = dataSource.getConnection().unwrap(PgConnection.class);
pgConnection.setAutoCommit(false);
try {
if (!getCopyToPriorSqlCommands().isEmpty()) {
LOG.info("Copy To - Prior commands - start");
for (String sql : getCopyToPriorSqlCommands()) {
pgConnection.execSQLUpdate(sql);
}
LOG.info("Copy To - Prior commands - finish");
}
try (BufferedReader br = new BufferedReader(new InputStreamReader(new ByteArrayInputStream(csvBytes)))) {
CopyManager cp = new CopyManager(pgConnection);
long rows = cp.copyIn(getCopyToSqlCommand(), br);
LOG.info("bulked copy to {} rows", rows);
}
if (getCopyToAfterSqlCommand() != null) {
LOG.info("Copy To - After command - start");
ResultSet rs = pgConnection.execSQLQuery(getCopyToAfterSqlCommand());
returnMsg = rs.getString(1);
rs.close();
LOG.info("Copy To - After command - finish. Result: {}", returnMsg);
if (!"OK".equals(returnMsg)) {
pgConnection.rollback();
}
}
} catch (PSQLException ex) {
pgConnection.rollback();
throw ex;
}
pgConnection.close();
In PostgreSQL log i can see the BEGIN, which is due to @Transactional annotation, then the truncate table command, then the COPY command, which is again within a transaction, so, there is BEGIN, and because of the COPY command is failed the ROLLBACK, and another ROLLBACK, which should roll back the @Transactional most outer BEGIN command.
but the table becomes empty, so the truncate table command somehow committed.