COMMIT / ROLLBACK

Commits or rolls back the current transaction within a procedure.

Syntax

COMMIT;
ROLLBACK;

Description

## Overview COMMIT and ROLLBACK provide explicit transaction control within PL/pgSQL blocks. In PostgreSQL, these statements end the current transaction and start a new one. In DeltaForge, they are parsed and accepted for compatibility but behave as no-ops in the current execution model. ## Behavior - COMMIT is logged via tracing at debug level with the message "COMMIT executed (no-op in current execution context)" and returns ControlFlow::Continue. Execution proceeds to the next statement. - ROLLBACK is logged similarly as a no-op and returns ControlFlow::Continue. - Both statements are fully parsed and represented in the AST (Statement::Commit, Statement::Rollback), so they do not cause parse errors. - In the current DataFusion-based execution model, there is no underlying transaction manager. Delta Lake operations use optimistic concurrency at the file level rather than traditional database transactions. ## Differences from PostgreSQL - In PostgreSQL, COMMIT inside a procedure ends the current transaction, releases all locks, and starts a new transaction. In DeltaForge, COMMIT is a no-op. - In PostgreSQL, ROLLBACK undoes all changes since the last COMMIT or SAVEPOINT. In DeltaForge, ROLLBACK is a no-op. DML changes that have already been written to Delta tables cannot be undone via ROLLBACK. - PostgreSQL disallows COMMIT/ROLLBACK inside anonymous DO blocks (only in procedures). DeltaForge accepts them anywhere but they have no effect. - For actual data rollback in DeltaForge, use Delta Lake's time travel or RESTORE TABLE functionality rather than relying on ROLLBACK.

Examples

DO $$
BEGIN
  INSERT INTO audit_log VALUES ('batch_start', now());
  COMMIT;
  INSERT INTO audit_log VALUES ('batch_end', now());
END;
$$;

DO $$
DECLARE
  rec RECORD;
  batch_count INT := 0;
BEGIN
  FOR rec IN SELECT id FROM large_table WHERE processed = false LIMIT 1000 LOOP
    UPDATE large_table SET processed = true WHERE id = rec.id;
    batch_count := batch_count + 1;
    IF batch_count % 100 = 0 THEN
      COMMIT;
      PRINT 'Committed batch at', batch_count;
    END IF;
  END LOOP;
  COMMIT;
  PRINT 'Final commit at', batch_count;
END;
$$;

DO $$
DECLARE
  success BOOLEAN := false;
BEGIN
  BEGIN
    INSERT INTO critical_table (data) VALUES ('test');
    success := true;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      PRINT 'Rolled back due to:', SQLERRM;
  END;
  IF success THEN
    COMMIT;
    PRINT 'Committed successfully';
  END IF;
END;
$$;

Pitfalls

• COMMIT and ROLLBACK are no-ops in DeltaForge. Do not rely on them for data integrity. DML changes are persisted as soon as the Delta Lake write completes.
• Code that depends on COMMIT to release locks or start new transactions will not behave as expected. DeltaForge uses optimistic concurrency, not lock-based transactions.
• Migrating PostgreSQL stored procedures that use COMMIT/ROLLBACK for batch processing should be refactored to use DeltaForge's idempotent write patterns instead.
• ROLLBACK inside an EXCEPTION handler does not undo prior DML. Use pre-checks or idempotent operations to handle error recovery.

See Also

• BLOCK
• EXCEPTION