Why an error in a transaction not rolled back when connecting to Amazon Redshift via JDBC connector?

Question

I understand that JDBC connector supports rollback. However, it was not rolled back when I intentionally generated an error in transaction of a process that combines COPY, TRUNCATE, and INSERT commands using Amazon Redshift's JDBC connector.

Why did it not roll back and what can I do about it?

Answer

Amazon Redshift JDBC driver's TRUNCATE command in JDBC connector commits transaction in which it is run.
Therefore, if an error occurs in a transaction that contains the TRUNCATE command, rollback processing may not be performed.

Please try changing the TRUNCATE command to the DELETE command and see if the rollback process succeeds.

For details on the TRUNCATE command, please refer to the following AWS documentation.

TRUNCATE - Amazon Redshift Database Developer Guide
https://docs.aws.amazon.com/en_us/redshift/latest/dg/r_TRUNCATE.html

The TRUNCATE command commits the transaction in which it is run; therefore, you can't roll back a TRUNCATE operation, and a TRUNCATE command may commit other operations when it commits itself.