本期问答如下：
- select for update问题
- 主备归档参数生效问题
- 循环外键约束问题
- 数据库管道操作问题
Q1. select for update问题
应用程序中有时并未使用数据库的UPSERT功能(insert on conflict),而是在insert发生错误后，通过捕获exception，然后使用select for update进行更新，这样其实有两个小问题需要注意。
第一个是insert捕获错误后，需要显式回滚才能继续进行后续操作。第二个问题是for update锁的粒度问题。
PG里有四种行锁，最初只有FOR UPDATE和FOR SHARE两种。FOR SHARE用于外键约束，使用外键在表中插入一行，PG会使用FOR SHARE锁定引用行来防止并发删除，这也会阻止并发更新。
9.4开始支持FOR KEY SHARE和FOR NO KEY UPDATE：
- FOR KEY SHARE用于insert引用的行上被使用。
- FOR NO KEY UPDATE用于不修改（主键或唯一键列）的更新。
FOR KEY SHARE和FOR NO KEY UPDATE可以互相不阻塞，因此大多数场景应该使用FOR NO KEY UPDATE，因为FOR UPDATE太昂贵，只适用于我们打算做delete操作或修改key的场景。
Q2. 主备环境归档参数生效问题
问题描述
在PG 12.4主备环境的archive_command参数设置如下：
archive_command = 'test ! -f /pgdata/archived/%f && cp %p /pgdata/archived/%f'
主库的归档命令生效，但备库却未生效，备库有什么参数控制该命令生效呢？
问题解答
可以通过archive_mode进行控制，除了禁用归档off值之外，有下面两种模式：
- on 只在主库归档
- always standby再次归档
archive_mode设置为always即可。
Q3. 循环外键约束问题
问题描述
使用pg_dumpall备份数据时出现如下错误提示。
$ pg_dumpall > /tmp/all.sql
pg_dump: warning: there are circular foreign-key constraints on this table:
问题解答
根据告警提示信息来看，数据库中存在循环外键约束，可能是指两个或多个表具有互相引用的外键约束，从而形成了一个循环。当存在这样的循环时，可能会使某些数据库操作变得困难，例如删除数据或者删除表。
使用-v查看详细日志，能够看到具体的表，截图中可以看到nodes表：
从数据库中去定位发现是repmgr扩展的元数据节点信息表
节点信息表的node_id与upstream_node_id有外键约束。
如果是完整恢复到一个新环境，可以忽略这个警告，并不会有数据丢失。
也可以修改循环引用的外键约束，或者使用pg_dump按单库进行备份，同时结合–exclude-table-data选项来排除受影响表的数据，示例如下：
$ pg_dump --exclude-table-data=repmgr.nodes > /tmp/all_ex.sql
生产环境中不建议在数据库中使用循环外键约束，因为它们可能导致潜在的数据完整性问题，并让维护变得更加困难。
另外可以使用下面的语句进行检查：
SELECT conrelid::regclass AS table_name,
conname AS constraint_name
FROM pg_constraint
WHERE contype = 'f'
AND conrelid = confrelid;
Q4. 数据库管道操作问题
问题描述
在PG 11.19 12.14 13.10 14.7 15.2当前最新的五个版本中，使用JDBC执行DROP DATABASE或CREATE DATABASE语句时，不能包含其他语句，否则会提示cannot be executed within a pipeline。
下面的第一条SQL语句在jdbc执行会提示错误信息：ERROR: DROP DATABASE cannot be executed within a pipeline
String sql = "SELECT 1;DROP DATABASE if exists mydb;";
第二条SQL语句在jdbc执行会提示错误信息：ERROR: CREATE DATABASE cannot be executed within a pipeline
String sql = "SELECT 1;CREATE DATABASE mydb;";
如果没有其他语句，DROP DATABASE可以与CREATE DATABASE一起执行，下面的语句执行不会报错
String sql = "DROP DATABASE if exists mydb;CREATE DATABASE mydb;";
问题解答
PG本身支持事务级的DDL操作，不过数据库的操作比较特殊，创建数据库时会立即触发检查点。
例如下面删除数据库及创建数据库不能在transaction里进行操作或者回滚：
postgres=# START TRANSACTION;
START TRANSACTION
postgres=*#
postgres=*# DROP DATABASE if exists mydb;
ERROR: DROP DATABASE cannot run inside a transaction block
postgres=!# end;
ROLLBACK
postgres=# START TRANSACTION;
START TRANSACTION
postgres=*# CREATE DATABASE mydb;
ERROR: CREATE DATABASE cannot run inside a transaction block
postgres=!# end;
ROLLBACK
上面的现象比较常见，数据库的创建及删除必须立即执行，不能进行事务控制。
PG 11.19 12.14 13.10 14.7 15.2这五个版本之前，数据库的创建及删除可以与其他语句混合执行，只要不显式进行事务控制即可。
这一行为的改变可能与11.19 12.14 13.10 14.7 15.2下面相关的更新有关：
In extended query protocol, avoid an immediate commit after ANALYZE if we’re running a pipeline (Tom Lane)
If there’s not been an explicit BEGIN TRANSACTION, ANALYZE would take it on itself to commit, which should not happen within a pipelined series of commands.
数据库的创建及删除应该独立执行，不与其他语句混合操作，CREATE DATABASE可以与DROP DATABASE if exists一起执行，先删除再创建，但不能进行事务操作。
