PostgreSQL 10.0 preview 功能增强 - 回滚范围可精细控制(事务、语句级)
背景
数据库的原子操作单位是事务，那么在一个事务中的SQL，正常情况下，应该是这样的：要么全部提交，要么全部回滚。
为了保证持久性，数据库还有一个概念，事务日志，每当事务提交时，都需要确保REDO持久化（REDO的写IO会比较多，IO延迟直接关系到tps吞吐量，特别是小事务）。
因此，有些业务，为了提高整体的吞吐（比如数据插入的吞吐），通常会使用分批提交的方式，比如一个事务中封装1000条INSERT语句。
但是问题来了，如果其中任何一条SQL失败，会导致这个事务之前执行的所有SQL全部回滚，如果用户只想回归当前异常的SQL，目前有以下几种方法。
1. 每条SQL后面加一个SAVE POINT，如果遇到异常SQL，回归到前一个SAVE POINT即可。
这个操作在psql客户端中有实现，需要设置这个变量
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 unset or set to off, 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.
psql
postgres=# \set ON_ERROR_ROLLBACK
相关代码，设置了ON_ERROR_ROLLBACK时，psql会自动在执行SQL前设置savepoint。
src/bin/psql/common.c
if (transaction_status == PQTRANS_INTRANS &&
pset.on_error_rollback != PSQL_ERROR_ROLLBACK_OFF &&
(pset.cur_cmd_interactive ||
pset.on_error_rollback == PSQL_ERROR_ROLLBACK_ON))
{
if (on_error_rollback_warning == false && pset.sversion < 80000)
{
char sverbuf[32];
psql_error("The server (version %s) does not support savepoints for ON_ERROR_ROLLBACK.\n",
formatPGVersionNumber(pset.sversion, false,
sverbuf, sizeof(sverbuf)));
on_error_rollback_warning = true;
}
else
{
results = PQexec(pset.db, "SAVEPOINT pg_psql_temporary_savepoint");
if (PQresultStatus(results) != PGRES_COMMAND_OK)
{
psql_error("%s", PQerrorMessage(pset.db));
ClearOrSaveResult(results);
ResetCancelConn();
goto sendquery_cleanup;
}
ClearOrSaveResult(results);
on_error_rollback_savepoint = true;
}
}
如果SQL执行失败，自动回归到SAVEPOINT
switch (transaction_status)
{
case PQTRANS_INERROR:
/* We always rollback on an error */
svptcmd = "ROLLBACK TO pg_psql_temporary_savepoint";
break;
如果SQL执行成功，自动释放savepoint
else
svptcmd = "RELEASE pg_psql_temporary_savepoint";
break;
对于使用JDBC驱动的用户，也支持这样的功能，不需要用户干预。
https://github.com/pgjdbc/pgjdbc/commit/adc08d57d2a9726309ea80d574b1db835396c1c8
1) If "DEALLOCATE" or "DISCARD" command status is observed, the driver would invalidate cached statements,
and subsequent executions would go through parse, describe, etc.
This feature is enabled by deafault.
2) If fails with "cached plan must not change result type", then re-parse might solve the problem.
However, if there a pending transaction, then the error would kill the transaction.
For that purpose, the driver sets a savepoint before each statement.
Automatic savepoint is configured via autosave property that can take the following values:
* conservative (default) -- rollback to savepoint only in case of "prepared statement does not exist" and
"cached plan must not change result type". Then the driver would re-execute the statement ant it would pass through
* never -- never set automatic safepoint. Note: in this mode statements might still fail with "cached plan must not change result type"
in autoCommit=FALSE mode
* always -- always rollback to "before statement execution" state in case of failure. This mode prevents "current transaction aborted" errors.
It is similar to psql's ON_ERROR_ROLLBACK.
The overhead of additional savepoint is like 3us (see #477).
引入savepoint会有一定的开销，所以PostgreSQL还有几种方法来提高高并发小事务的性能，比如异步提交，分组提交。
1. 异步提交
commit时，不等待redo落盘即返回，从而提升小事务吞吐。PostgreSQL的异步提交并不会造成数据的不一致，因为shared buffer里面的脏页在刷盘前，会确保对应的REDO页先落盘。
但是异步提交也有一定的风险，比如数据库crash，redo buffer中的没有落盘的事务会回滚（即使事务已提交）。好在PostgreSQL wal writer进程的调度是非常紧密的，最大10毫秒调度刷一次redo buffer。
2. group commit
组提交，也是常用手段，将同时提交的事务的REDO IO请求合并成1个请求，从而减少高并发小事务的REDO IO写请求量。提升小事务的吞吐率。
组提交只在高并发时才能发挥效果，异步提交可以在任意场景发挥效果。
组提交相比异步提交的好处, 不会造成数据丢失。
《PostgreSQL 可靠性分析 - 关于redo block原子写》
说完前面的，进入正题，savepoint是客户端行为，而不是服务端行为，因为客户端需要在每一次QUERY发生前后开启和释放SAVEPOINT。虽然一些驱动封装了这个功能。
那么数据库本身能提供这样的功能吗？
PostgreSQL 10.0 服务端自动savepoint
10.0 将加入一个语法，启动事务时，指定该事务发生异常时，要求语句级别回滚还是事务级别回滚。
如果选择了语句级别回滚，那么当提交的SQL发生异常时，可以继续后面的SQL，否则必须回滚整个事务。
语法如下
START TRANSACTION ROLLBACK SCOPE { TRANSACTION | STATEMENT }
讨论详情
Hello,
As I stated here and at the PGConf.ASIA developer meeting last year, I'd like to propose statement-level rollback feature. To repeat myself, this is requested for users to migrate from other DBMSs to PostgreSQL. They expect that a failure of one SQL statement should not abort the entire transaction and their apps (client programs and stored procedures) can continue the transaction with a different SQL statement.
SPECIFICATION
==================================================
START TRANSACTION ROLLBACK SCOPE { TRANSACTION | STATEMENT };
This syntax controls the behavior of the transaction when an SQL statement fails. TRANSACTION (default) is the traditional behavior (i.e. rolls back the entire transaction or subtransaction). STATEMENT rolls back the failed SQL statement.
Just like the isolation level and access mode, default_transaction_rollback_scope GUC variable is also available.
DESIGN
==================================================
Nothing much to talk about... it merely creates a savepoint before each statement execution and destroys it after the statement finishes. This is done in postgres.c for top-level SQL statements.
The stored function hasn't been handled yet; I'll submit the revised patch soon.
CONSIDERATIONS AND REQUESTS
==================================================
The code for stored functions is not written yet, but I'd like your feedback for the specification and design based on the current patch. I'll add this patch to CommitFest 2017-3.
The patch creates and destroys a savepoint for each message of the extended query protocol (Parse, Bind, Execute and Describe). I'm afraid this will add significant overhead, but I don't find a better way, because those messages could be send arbitrarily for different statements, e.g. Parse stmt1, Parse stmt2, Bind stmt1, Execute stmt1, Bind stmt2, Execute stmt2.
Regards
Takayuki Tsunakawa
这个patch的讨论，详见邮件组，本文末尾URL。
PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。
参考
https://commitfest.postgresql.org/14/1050/
https://www.postgresql.org/message-id/flat/0A3221C70F24FB45833433255569204D1F6A9286@G01JPEXMBYT05#0A3221C70F24FB45833433255569204D1F6A9286@G01JPEXMBYT05
https://github.com/pgjdbc/pgjdbc/commit/adc08d57d2a9726309ea80d574b1db835396c1c8