4. Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

Transaction management is a critical aspect of database programming, allowing for controlled execution of data operations. Both PL/SQL in Oracle and PL/pgSQL in PostgreSQL offer transaction management capabilities, but there are some differences in syntax and behavior.

PL/SQL Transaction Management

In Oracle’s PL/SQL, transactions are typically managed using the COMMIT, ROLLBACK, and SAVEPOINT statements. Transactions are implicit, meaning that each block execution can be a part of a transaction.

Example of Transaction Management in PL/SQL

BEGIN

    -- Start of transaction

    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

    -- Set a savepoint

    SAVEPOINT update_salary;

    -- Another update

    UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

    -- Rollback to savepoint if necessary

    ROLLBACK TO update_salary;

    -- Commit the transaction

    COMMIT;

END;

In this example:

• SAVEPOINT is used to mark a point in the transaction to which you can roll back.
• ROLLBACK TO allows you to undo changes up to the savepoint, without affecting earlier operations.
• COMMIT finalizes all changes made within the transaction.

PL/pgSQL Transaction Management

In PostgreSQL, transaction management within PL/pgSQL functions and procedures operates slightly differently. While you can use COMMIT and ROLLBACK in standalone PL/pgSQL blocks, they cannot be used directly within functions, as functions must run within a single transaction context. However, transaction management is possible using procedures, which are designed to support transactional control.

Example of Transaction Management in PL/pgSQL

BEGIN;

-- Start of transaction

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

-- Set a savepoint

SAVEPOINT update_salary;

-- Another update

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

-- Rollback to savepoint if needed

ROLLBACK TO update_salary;

-- Commit the transaction

COMMIT;

For procedures:

CREATE PROCEDURE adjust_salaries()

LANGUAGE plpgsql

AS $$

BEGIN

    -- Start of transaction

    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

    -- Set a savepoint

    SAVEPOINT update_salary;

    -- Another update

    UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

    -- Rollback to savepoint if needed

    ROLLBACK TO update_salary;

    -- Commit the transaction

    COMMIT;

END;

$$;

In this example:

• Explicit transactions can be managed directly in procedures.
• Savepoints can be set and rolled back as needed, similar to PL/SQL.
• While functions run within a single transaction, procedures in PostgreSQL allow the use of transaction control commands like COMMIT and ROLLBACK.

Key Differences

• Functions vs. Procedures: In PostgreSQL, transaction management commands can only be used in procedures, not functions, whereas PL/SQL allows these commands in both contexts. Please note, however, that functions in PostgreSQL automatically run within a single transaction context. 
• Implicit Transactions: Oracle’s PL/SQL handles transactions implicitly within blocks, whereas PostgreSQL’s PL/pgSQL requires explicit transaction management when using procedures.
• SAVEPOINT Behavior: The behavior of savepoints is largely similar in both systems, offering a way to partially undo transactions without affecting the entire block.

Best Practices for Transaction Management in PL/pgSQL

• Use procedures for complex transaction control: If you need to manage transactions with commits or rollbacks, consider refactoring functions into procedures in PostgreSQL.
• Minimize transaction duration: Keep transactions short to avoid locking issues and to improve performance.
• Handle exceptions carefully: Use the EXCEPTION block to catch and handle errors, ensuring transactions are properly rolled back when needed.