Transactions handling

Informix^® and PostgreSQL handle transactions in a similar manner.

Informix native mode (non ANSI):

Transactions are started with BEGIN WORK.
Transactions are validated with COMMIT WORK.
Transactions are canceled with ROLLBACK WORK.
Savepoints can be set with SAVEPOINT name [UNIQUE].
Transactions can be rolled back to a savepoint with ROLLBACK [WORK] TO SAVEPOINT [ name ].
Savepoints can be released with RELEASE SAVEPOINT name.
Statements executed outside of a transaction are automatically committed.
DDL statements can be executed (and canceled) in transactions.

PostgreSQL supports transaction with savepoints:

Transactions are started with BEGIN WORK.
Transactions are validated with COMMIT WORK.
Transactions are canceled with ROLLBACK WORK.
Savepoints can be placed with SAVEPOINT name .
Transactions can be rolled back to a savepoint with ROLLBACK TO SAVEPOINT name .
Savepoints can be released with RELEASE SAVEPOINT name .
Statements executed outside of a transaction are automatically committed.
DDL statements can be executed (and canceled) in transactions.
If an SQL error occurs in a transaction, the whole transaction is aborted.

Transactions in stored procedures: avoid using transactions in stored procedures to allow the client applications to handle transactions, according to the transaction model.

The main difference between Informix and PostgreSQL resides in the fact that PostgreSQL cancels the entire transaction if an SQL error occurs in one of the statements executed inside the transaction. The following code example illustrates this difference:

CREATE TABLE tab1 ( k INT PRIMARY KEY, c CHAR(10) )
WHENEVER ERROR CONTINUE
BEGIN WORK
INSERT INTO tab1 ( 1, 'abc' )
INSERT INTO tab1 ( 1, 'abc' )
    -- PK constraint violation = SQL Error, whole TX is aborted
COMMIT WORK

With Informix, this code will leave the table with one row inside, since the first INSERT statement succeeded. With PostgreSQL, the table will remain empty after executing this piece of code, because the server will rollback the whole transaction. To workaround this problem in PostgreSQL you can use SAVEPOINT as described in Solution.

Solution

Informix transaction handling commands are automatically converted to PostgreSQL instructions to start, validate or cancel transactions.

Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with PostgreSQL.

You must review the SQL statements inside BEGIN WORK / COMMIT WORK instruction and check if these can raise an SQL error. The SQL statements that can potentially raise an SQL error must be protected with a SAVEPOINT. If an error occurs, just rollback to the savepoint:

CREATE TABLE tab1 ( k INT PRIMARY KEY, c CHAR(10) )
WHENEVER ERROR CONTINUE
BEGIN WORK
INSERT INTO tab1 ( 1, 'abc' )
CALL sql_protect()
INSERT INTO tab1 ( 1, 'abc' )
 -- PK constraint violation = SQL Error
CALL sql_unprotect()
COMMIT WORK
...
FUNCTION sql_protect()
  IF NOT dbtype == "PGS" THEN
     RETURN
  END IF
  SAVEPOINT _sql_protect_
END FUNCTION

FUNCTION sql_unprotect()
  IF NOT dbtype == "PGS" THEN
     RETURN
  END IF
  IF SQLCA.SQLCODE < 0 THEN
     ROLLBACK TO SAVEPOINT _sql_protect_
  ELSE
     RELEASE SAVEPOINT _sql_protect_
  END IF
END FUNCTION

Note: If you want to use savepoints, do not use the UNIQUE keyword in the savepoint declaration, always specify the savepoint name in ROLLBACK TO SAVEPOINT, and do not drop savepoints with RELEASE SAVEPOINT.