PG模拟DB2/Oracle事务中语句级报错行为

本期播客

PG模拟DB2/Oracle事务中语句级报错行为的插件 pg_statement_rollback

使用PG时, 如果在一个事务中有语句执行报错, 根据事务的原子性, 整个事务将回滚.

这没啥毛病.

如果你想回滚到报错的语句之前, 可借助savepoint, 但是这需要在每个语句执行后都创建savepoint, 可能产生很多子事务, 或者带来性能问题, 使用时也非常不便利(除非框架支持自动创建savepoint, 可简化使用).

例如psql客户端可通过设置ON_ERROR_ROLLBACK变量来自动创建savepoint

-v, --set=, --variable=NAME=VALUE  
set psql variable NAME to VALUE  
                           (e.g., -v ON_ERROR_STOP=1)  

ON_ERROR_ROLLBACK 变量详解如下:

When set to on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues.

When set to interactive, such errors are only ignored in interactive sessions, and not when reading script files.

When set to off (the default), a statement in a transaction block that generates an error aborts the entire transaction.

The error rollback mode works by issuing an implicit SAVEPOINT for you, just before each command that is in a transaction block, and then rolling back to the savepoint if the command fails.

那用户自己开发的程序呢, 其实也可以通过JDBC和ODBC的配置实现, 但是这些实现有个问题, 需要客户端(虽然是通过驱动)发起savepoint和release savepoint, 导致了更多的通信负担, 会影响性能.

幸好有个pg_statement_rollback插件可以试试, 这个插件是数据库服务端自动实现, 不需要客户端主动发起请求. 性能更好.

https://github.com/HexaCluster/pg_statement_rollback

pg_statement_rollback 概述

pg_statement_rollback 是 PostgreSQL 的扩展，用于添加服务器端事务，并在语句级别进行回滚，就像在 Oracle 或 DB2 中一样。

如果 SQL 语句在执行过程中的任何时候发生错误，则该语句的所有影响都将被回滚。回滚的效果就像该语句从未运行过一样。此操作称为语句级回滚，具有以下特点：

• 执行失败的 SQL 语句仅会导致其自身本应执行的工作丢失。不成功的语句不会导致当前事务中之前执行的任何工作的丢失。
• 回滚的效果就像该语句从未运行过一样。

在 PostgreSQL 中，如果遇到错误，事务将无法继续，并且事务中完成的所有工作都会被回滚。Oracle 或 DB2 在每个语句执行前都有隐式保存点，允许回滚到语句失败之前的状态。

目前，PostgreSQL 语句级回滚的实现是在客户端完成的。psql 有 \set ON_ERROR_ROLLBACK on，JDBC 在执行query时出现 SQL 异常时具有自动回滚功能，psql ODBC 也具有 “语句级回滚” 模式。这些实现的问题在于，它们通过发送SAVEPOINT autosave和RELEASE SAVEPOINT autosave增加了与服务器的额外通信，因此会严重限制应用程序的SQL吞吐。

pg_statement_rollback 扩展会在服务器端自动执行savepoint操作，这只会对性能造成非常有限的影响（请参阅下文的“性能”章节）。当然，当发生错误时，您仍然需要在客户端管理“ROLLBACK TO SAVEPOINT”操作。例如：

BEGIN;  
CREATE TABLE savepoint_test(id integer);  
INSERT INTO savepoint_test SELECT 1;  
SELECT COUNT( * ) FROM savepoint_test; -- return 1  
INSERT INTO savepoint_test SELECT 'wrong 1'; -- generate an error  
-- Handle the error and fall back to previous statement.  
ROLLBACK TO "PgSLRAutoSvpt";  
SELECT COUNT( * ) FROM savepoint_test; -- still return 1  
ROLLBACK;  

如果没有此扩展，整个事务都将被取消，所有语句将回滚：

ERROR:  current transaction is aborted, commands ignored until end of transaction block  

以下是启用语句级回滚的测试输出：

BEGIN  
CREATE TABLE  
INSERT 0 1  
 count   
-------  
     1  
(1 row)  

psql:toto.sql:9: ERROR:  invalid input syntax fortypeinteger: "wrong 1"
LINE 1: INSERT INTO savepoint_test SELECT 'wrong 1';  
                                          ^  
ROLLBACK  
 count   
-------  
     1  
(1 row)  

ROLLBACK  

安装

要安装 pg_statement_rollback 扩展，您至少需要 PostgreSQL 9.5 版本。请将 pg_statement_rollback 压缩包解压到任何您想要的位置，然后使用 PGXS 进行编译。编译时pg_config命令必须位于您的PATH中。

根据您的安装情况，您可能需要安装一些开发包。请执行以下操作：

USE_PGXS=1 make  
USE_PGXS=1 sudo make install  

要运行测试，请以超级用户身份执行以下命令：

USE_PGXS=1 make installcheck  

配置

服务器端自动保存点

pg_statement_rollback.enabled  

可以使用此 GUC 启用/禁用扩展程序，默认启用。要禁用扩展程序，请使用：

SET pg_statement_rollback.enabled TO off;  

您可以在会话中的任何时候禁用或启用扩展。

pg_statement_rollback.savepoint_name  

默认情况下，使用的内部保存点是：PgSLRAutoSvpt 如果您想更改名称，可以使用此 GUC。例如：

SET pg_statement_rollback.savepoint_name TO 'my_new_sp_name';  

那么你将使用 ROLLBACK TO SAVEPOINT my_new_sp_name; 。

该参数只能由超级用户设置。

pg_statement_rollback.enable_writeonly  

默认情况下，扩展不会在 SELECT 语句后发出自动保存点。这是为了限制保存点的数量，避免填满子事务缓存。如果事务中的语句少于 64 个（PGPROC_MAX_CACHED_SUBXIDS），则可以禁用此行为而不会影响性能。否则，由于 pg_subtrans 的磁盘扫描，您将会体验到一些性能损失。

检测对 CTE 或带有嵌套写入语句的函数的调用，并在执行主语句后执行自动savepoint。

您可以通过将pg_statement_rollback.enabled设置为 off 来禁用此功能。例如，如果您调用自定义 C 函数，而该函数直接写入未被检测为写入语句的表。

扩展的使用

在所有想要使用 pg_statement_rollback 事务并在语句级别进行回滚的会话中，您必须使用以下命令加载扩展：

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

然后在您的应用程序中，当出现错误时，您必须调用

ROLLBACK TO SAVEPOINT "PgSLRAutoSvpt";  

以发生错误之前的状态继续运行事务。

如果您想要推广使用扩展，请修改您的 postgresql.conf 以设置

session_preload_libraries = 'pg_statement_rollback'

并添加

pg_statement_rollback.enabled = on  

请参阅 Github项目 test/sql/ 中的文件以获取一些使用示例。

性能

Without loading the extension

$ pgbench -h localhost bench -c 20 -j 8 -T 30  
starting vacuum...end.  
transaction type: <builtin: TPC-B (sort of)>  
scaling factor: 1  
query mode: simple  
number of clients: 20  
number of threads: 8  
duration: 30 s  
number of transactions actually processed: 22298  
latency average = 26.932 ms  
tps = 742.603558 (including connections establishing)  
tps = 742.811083 (excluding connections establishing)  

With the use of the extension and write only mode enabled

$ pgbench -h localhost bench -c 20 -j 8 -T 30  
starting vacuum...end.  
transaction type: <builtin: TPC-B (sort of)>  
scaling factor: 1  
query mode: simple  
number of clients: 20  
number of threads: 8  
duration: 30 s  
number of transactions actually processed: 21679  
latency average = 27.702 ms  
tps = 721.980032 (including connections establishing)  
tps = 722.188840 (excluding connections establishing)  

With the use of the extension and write only mode disabled

$ pgbench -h localhost bench -c 20 -j 8 -T 30  
starting vacuum...end.  
transaction type: <builtin: TPC-B (sort of)>  
scaling factor: 1  
query mode: simple  
number of clients: 20  
number of threads: 8  
duration: 30 s  
number of transactions actually processed: 21677  
latency average = 27.705 ms  
tps = 721.903735 (including connections establishing)  
tps = 722.126272 (excluding connections establishing)  

实际上，这里使用的 pgbench 场景对于测试将保存点限制为仅写入语句的效果并不实用。应该构建一个特殊脚本，并在一个事务中包含超过 64 个（PGPROC_MAX_CACHED_SUBXIDS）个语句，以开始测试瓶颈。