pg_statement_rollback

Overview

Version

Build

You can build the DEB packages for pg_statement_rollback using pig build:

pig build pkg pg_statement_rollback         # build DEB packages

Install

You can install pg_statement_rollback directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_statement_rollback;          # Install for current active PG version

pig ext install -y pg_statement_rollback -v 18  # PG 18
pig ext install -y pg_statement_rollback -v 17  # PG 17
pig ext install -y pg_statement_rollback -v 16  # PG 16
pig ext install -y pg_statement_rollback -v 15  # PG 15
pig ext install -y pg_statement_rollback -v 14  # PG 14

dnf install -y pg_statement_rollback_18       # PG 18
dnf install -y pg_statement_rollback_17       # PG 17
dnf install -y pg_statement_rollback_16       # PG 16
dnf install -y pg_statement_rollback_15       # PG 15
dnf install -y pg_statement_rollback_14       # PG 14

apt install -y postgresql-18-pg-statement-rollback   # PG 18
apt install -y postgresql-17-pg-statement-rollback   # PG 17
apt install -y postgresql-16-pg-statement-rollback   # PG 16
apt install -y postgresql-15-pg-statement-rollback   # PG 15
apt install -y postgresql-14-pg-statement-rollback   # PG 14

Preload:

shared_preload_libraries = 'pg_statement_rollback';

Usage

pg_statement_rollback: Server side rollback at statement level for PostgreSQL like Oracle or DB2

Provides automatic server-side savepoints before each statement, allowing individual statement failures without aborting the entire transaction.

Enabling

LOAD 'pg_statement_rollback.so';
SET pg_statement_rollback.enabled TO on;

Or in postgresql.conf for all sessions:

session_preload_libraries = 'pg_statement_rollback'
pg_statement_rollback.enabled = on

Basic Usage

BEGIN;
CREATE TABLE test(id integer);
INSERT INTO test SELECT 1;
SELECT COUNT(*) FROM test;         -- returns 1

INSERT INTO test SELECT 'wrong';   -- ERROR: invalid input syntax
ROLLBACK TO SAVEPOINT "PgSLRAutoSvpt";  -- rollback only the failed statement

SELECT COUNT(*) FROM test;         -- still returns 1
COMMIT;

Without this extension, the error would abort the entire transaction and all subsequent statements would fail with “current transaction is aborted”.

Configuration

-- Enable/disable at any time in a session
SET pg_statement_rollback.enabled TO off;

-- Change the savepoint name (superuser only)
SET pg_statement_rollback.savepoint_name TO 'my_savepoint';

-- Limit savepoints to write-only statements (default: on)
SET pg_statement_rollback.enable_writeonly TO off;

Key Behaviors

• Automatic savepoints are created server-side with minimal performance overhead
• By default, savepoints are only created after write statements (INSERT, UPDATE, DELETE, etc.)
• When enable_writeonly is on, SELECT statements do not trigger automatic savepoints
• The client must still call ROLLBACK TO SAVEPOINT "PgSLRAutoSvpt" when handling errors