本期整理的几个问题如下：
- 保存点与自动清理保存点
- RETURNING字段与双引号嵌套
- 处理NUll值的两个特性
- openGauss基础备份如何设置交互密码
一、保存点与自动清理保存点
在psql里有一个内置的变量ON_ERROR_ROLLBACK，它允许事务块内的语句遇到错误时，错误是否可以被忽略，不回滚整个事务而继续执行。
下面左右两个窗口演示了ON_ERROR_ROLLBACK变量分别设置为off和on后，事务块内语句执行的区别。
左边窗口ON_ERROR_ROLLBACK设置为off，这是默认行为，一旦语句执行出错，将导致整个事务块被abort。通过PROMPT2提示符的%x也可以跟踪事务运行的状态：显示!表示事务失败。
右边窗口ON_ERROR_ROLLBACK设置为on，语句执行出错可以被忽略，事务继续，整个事务块可以提交成功。它的原理是对事务块里的每个语句执行前都创建一个SAVEPOINT保存点，遇到语句执行出错后ROLLBACK到失败语句的保存点。
ON_ERROR_ROLLBACK变量还可以设置为interactive，这也是更为推荐的设置值，它只对交互式会话生效，读取外部脚本文件时不起作用，演示如下：
右边窗口读取外部脚本文件时，ON_ERROR_ROLLBACK相当于off。
在session的交互式模式下设置ON_ERROR_ROLLBACK变量，遇到错误时自动ROLLBACK到之前的语句，再继续工作：
\set ON_ERROR_ROLLBACK interactive
JDBC里也有一个autosave连接可以设置，功能类似于psql里的ON_ERROR_ROLLBACK。
如果不使用autosave，应用端可能会遇到如下的错误提示信息：
current transaction is aborted, commands ignored until end of transaction block
使用autosave=always参数进行批量插入操作时，也可能遇到导入较慢的问题，这是由于创建较多的保存点引起，因此还需要搭配cleanupSavepoints=true参数及时清理释放保存点。
注意：从JDBC 42.2.6才开始支持cleanupSavepoints参数，如果使用较低版本的JDBC需要升级一下驱动。
二、RETURNING字段与双引号嵌套
使用RETURNING关键字可以优化多个SQL语句合并为一个，一次性返回来提高性能，示例参考：[译] CUD操作一次性返回行
在JDBC里，有如下几种方式去返回字段：
- 方式一：使用RETURN_GENERATED_KEYS回显自增主键，参考代码如下：
PreparedStatement stmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
stmt.executeUpdate();
ResultSet keys = stmt.getGeneratedKeys();
while(keys.next()) {
System.out.println(keys.getInt(1));
}
- 方式二：SQL语句里显式使用RETURNING，参考代码如下：
String sql =
"insert into test_returning (name) values('PostgreSQL') returning id";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
int returnId = rs.getInt("id");
System.out.println("插入的主键ID为:" + returnId);
}
- 方式三：Statement里显式传递返回字段列表，参考代码如下：
String sql =
"insert into test_returning (name) values('PostgreSQL')";
PreparedStatement stmt = conn.prepareStatement(sql,new String[] {"ID"});
而如果我们传递的参数恰好使用了大写字段，此时会出现找不到字段的错误提示：
ERROR: column "ID" does not exist
这种场景一般是使用ORM框架时会遇到，该方式下JDBC对返回的字段嵌套了一层双引号，大写的字段与表的元数据字段不匹配，因此提示错误。
JDBC也为我们提供了该场景下的连接参数控制：quoteReturningIdentifiers
jdbc:postgresql://ip:port/dbname?quoteReturningIdentifiers=false
当应用程序使用大写字段命中该场景时，可以设置quoteReturningIdentifiers=false来去除嵌套的双引号。
三、处理NUll值的两个特性
如下的两个特性可以让我们对NULL值的处理更加合情合理：
- 需要保证唯一的字段使用UNIQUE NULLS DISTINCT
- 多个外键引用字段使用num_nulls/num_nonnulls函数定义约束
由于NULL != NULL，UNIQUE字段允许有多个NULL值，从版本15开始我们可以控制UNIQUE字段是否允许有多个NULL值，语法如下：
UNIQUE NULLS DISTINCT UNIQUE NULLS NOT DISTINCT
例如下面tab1表的column1字段将只允许插入一个NULL值。
CREATE TABLE tab1(
column1 VARCHAR,
UNIQUE NULLS NOT DISTINCT(column1)
);
如果一个表有多个外键引用字段时，可以使用num_nulls/num_nonnulls函数更好的定义约束，例如只允许一个字段非空
CHECK (num_nonnulls(column1, column2, column3) = 1);
四、openGauss基础备份如何设置交互密码
在openGauss数据库里使用gs_basebackup进行本地备份时可以不用输入密码，服务端本地访问是可信的，不过远程备份时需要输入密码。
$ gs_basebackup -D /backup -h x.x.x.x -p 5432 -U repuser -W
社区的YANG Lei同学写了一个expect脚本, 可以代替输入密码。
$ more mybasebackup.sh
#!/usr/bin/expect -f
# Check if the correct number of arguments is provided
if {$argc != 3} {
puts "Usage: $argv0 remote_host remote_port local_backup_dir"
exit 1
}
set remote_host [lindex $argv 0]
set remote_port [lindex $argv 1]
set local_backup_dir [lindex $argv 2]
spawn gs_basebackup -h $remote_host -p $remote_port -D $local_backup_dir -U hgdbadm -X stream -P -v -W
expect {
"Password:" {
send "Softswitch1\r"
exp_continue
}
eof {
}
}
catch wait result
exit [lindex $result 3]
