If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement had never been run. This operation is called statement-level rollback and has the following characteristics:

In LightDB the transaction cannot continue when you encounter an error and the entire work done in the transaction is rolled back. Oracle have implicit savepoint before each statement execution which allow a rollback to the state just before the statement failure. Current implementation of rollback at statement level for LightDB are done at client side. ltsql has \set ON_ERROR_ROLLBACK on, JDBC has autorollback on SQL exception from executing a query, ltsqlODBC too with the "statement level rollback" mode. The problem of these implementations is that they add extra communication with the server by sending a SAVEPOINT autosave and RELEASE SAVEPOINT autosave so it can seriously limit the throughput of the application. The lt_statement_rollback extension execute the automatic savepoint at server side which adds a very limited penalty to the performances.

lt_statement_rollback extension need to run in Oracle/MySQL compatibility mode. And session level switches need to be configured (not enabled by default).

# lightdb.conf
lt_statement_rollback.enabled=on
            

you must add lt_statement_rollback in shared_preload_libraries, and restart server is needed after modify shared_preload_libraries.

 
# lightdb.conf
shared_preload_libraries = 'lt_statement_rollback'

lightdb@test=# CREATE EXTENSION lt_statement_rollback;

BEGIN;
CREATE TABLE lt_statement_rollback_test(id integer);
INSERT INTO lt_statement_rollback_test VALUES(1);
SELECT COUNT(*) FROM lt_statement_rollback_test; -- return 1
INSERT INTO lt_statement_rollback_test VALUES('wrong data'); -- generate an error
-- Handle the error and fall back to previous statement.
SELECT COUNT(*) FROM lt_statement_rollback_test; -- still return 1
COMMIT;
    

Without the extension everything will be cancelled and statement after the error on INSERT will return:

ERROR:  current transaction is aborted, commands ignored until end of transaction block
    

Here is the output of the test with statement-level rollback enabled:

lightdb@test=# BEGIN;
BEGIN
lightdb@test=*# CREATE TABLE lt_statement_rollback_test(id integer);
CREATE TABLE
lightdb@test=*# INSERT INTO lt_statement_rollback_test VALUES(1);
INSERT 0 1
lightdb@test=*# SELECT COUNT(*) FROM lt_statement_rollback_test; -- return 1
 count 
-------
     1
(1 row)

lightdb@test=*# INSERT INTO lt_statement_rollback_test VALUES('wrong data'); -- generate an error
ERROR:  invalid input syntax for type integer: "wrong data"
LINE 1: INSERT INTO lt_statement_rollback_test VALUES('wrong data');
                                                      ^
lightdb@test=!# -- Handle the error and fall back to previous statement.
lightdb@test=!# SELECT COUNT(*) FROM lt_statement_rollback_test; -- still return 1
 count 
-------
     1
(1 row)

lightdb@test=*# COMMIT;
COMMIT