4.6 Transaction Control

Table of Contents Previous Next


4 Stored Procedure Language : 4.6 Transaction Control

An SPL application can be created that uses a style of transaction control compatible with Oracle databases if the following conditions are met:
The edb_stmt_level_tx parameter must be set to TRUE. This prevents the action of unconditionally rolling back all database updates within the BEGIN/END block if any exception occurs. See Section 1.3.4 for more information on the edb_stmt_level_tx parameter.
A transaction begins when the first SQL command is encountered in the SPL program. All subsequent SQL commands are included as part of that transaction. The transaction ends when one of the following occurs:
A COMMIT command is encountered in which case the effect of all database updates made during the transaction become permanent.
A ROLLBACK command is encountered in which case the effects of all database updates made during the transaction are rolled back and the transaction is aborted. If a new SQL command is encountered, a new transaction begins.
Control returns to the calling application (such as Java, PSQL, etc.) in which case the action of the application determines whether the transaction is committed or rolled back.
Note: Unlike Oracle, DDL commands such as CREATE TABLE do not implicitly occur within their own transaction. Therefore, DDL commands do not automatically cause an immediate database commit as in Oracle, and DDL commands may be rolled back just like DML commands.
A transaction may span one or more BEGIN/END blocks, or a single BEGIN/END block may contain one or more transactions.
The following sections discuss the COMMIT and ROLLBACK commands in more detail.
4.6.1 COMMIT
The COMMIT command makes all database updates made during the current transaction permanent, and ends the current transaction.
The COMMIT command may be used within anonymous blocks, stored procedures, or functions. Within an SPL program, it may appear in the executable section and/or the exception section.
In the following example, the third INSERT command in the anonymous block results in an error. The effect of the first two INSERT commands are retained as shown by the first SELECT command. Even after issuing a ROLLBACK command, the two rows remain in the table as shown by the second SELECT command verifying that they were indeed committed.
Note: The edb_stmt_level_tx configuration parameter shown in the example below can be set for the entire database using the ALTER DATABASE command, or it can be set for the entire database server by changing it in the postgresql.conf file.
4.6.2 ROLLBACK
The ROLLBACK command undoes all database updates made during the current transaction, and ends the current transaction.
The ROLLBACK command may be used within anonymous blocks, stored procedures, or functions. Within an SPL program, it may appear in the executable section and/or the exception section.
In the following example, the exception section contains a ROLLBACK command. Even though the first two INSERT commands are executed successfully, the third results in an exception that results in the rollback of all the INSERT commands in the anonymous block.
The following is a more complex example using both COMMIT and ROLLBACK. First, the following stored procedure is created which inserts a new employee.
The following anonymous block is run. Note the use of the COMMIT command after all calls to the emp_insert procedure and the ROLLBACK command in the exception section.
The following SELECT command shows that employees Farrell and Tyler were successfully added.
A SELECT command run against the table yields the following:
The ROLLBACK command in the exception section successfully undoes the insert of employee Harrison. Also note that employees Farrell and Tyler are still in the table as their inserts were made permanent by the COMMIT command in the first anonymous block.

4 Stored Procedure Language : 4.6 Transaction Control

Table of Contents Previous Next