我们继续关注即将发布的 PostgreSQL 16 的消息。第三届 CommitFest 于 12 月初结束。让我们看看结果。
以下是我想谈的补丁：
- 介子：一个新的源代码构建系统
- 文档：事务处理的新篇章
- psql: \d+ 表示分区表中的外部分区
- psql：扩展查询协议支持
- 物化视图上的谓词锁
- 跟踪索引和表的上次扫描时间
- pg_buffercache：一个新函数 pg_buffercache_summary
- walsender 在进程状态中显示数据库名称
- 减少冻结元组的 WAL 开销
- 空闲时功耗降低
- postgres_fdw：COPY 的批处理模式
- 使 GUC 基础设施现代化
- 哈希索引构建优化
- MAINTAIN ― 表维护的新权限
- SET ROLE：更好的角色变更管理
- 支持 pg_hba.conf 和 pg_ident.conf 中的文件包含指令
- pg_hba.conf 中的正则表达式支持
meson：来自源代码提交的新构建
提交： e6927270，4c72102e
现在可以使用新的构建系统 - 介子从源代码构建服务器。第一次提交发生在九月份。数十人紧随其后。在 12 月初，出现了一个提交，在文档中添加了一个部分来描述新的构建过程。wiki中还有一个页面，除其他内容外，还说明了这一决定背后的原因。而其中一个重要的原因是在 Windows 下构建服务器的简化以及将来拒绝支持位于 src/tools/msvc 目录中的本机工具。
除了 autoconf 和 make 之外，还出现了一个新的构建系统。我们不是在谈论废除经过验证的旧工具。
但就本文而言，最新的第 16 版服务器是由 meson 组装的。Ubuntu下的构建过程如下：
$ git clone git://git.postgresql.org/git/postgresql.git
$ cd postgresql
$ meson setup build --prefix=/home/pal/pg16 -Dpgport=5416
$ cd build
$ ninja
$ ninja install
如您所见，使用默认参数，除了安装目录和服务器端口。
剩下的就是初始化数据库集群和启动服务器。这些步骤不再依赖于构建系统。
文档：事务处理新章节
提交： 66bc9d2d
文档中出现了一个新章节：74.Transaction processing。一小章位于“内部设备”部分。它简要概述了虚拟和真实事务 ID、它们到 pg_locks 的映射以及嵌套事务和两阶段事务的实现。
psql: \d+ 分配分区表的外部表分区
提交： bd95816f
对于分区表，\d+ 命令将使用 FOREIGN 一词将分区标记为外部表。
\d+ data
Partitioned table "public.data"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+------+-----------+----------+---------+----------+-------------+--------------+-------------
data_year | text | | | | extended | | |
Partition key: LIST (data_year)
Partitions: data_2020 FOR VALUES IN ('2020'), FOREIGN,
data_2021 FOR VALUES IN ('2021'), FOREIGN,
data_2022 FOR VALUES IN ('2022')
直到现在，psql 还没有直接支持高级查询协议。因此，参数化查询只能间接执行，例如通过 PREPARE 语句。
\bind 命令现在已添加到 psql 中，用于将值绑定到下一个查询的参数。它是这样工作的：
SET log_statement='all';
\bind 42 'Answer: '
SELECT $2||$1;
?column?
------------
Answer: 42
(1 row)
\! tail -2 logfile
2022-12-05 12:58:05.924 MSK [16566] LOG: execute <unnamed>: SELECT $2||$1;
2022-12-05 12:58:05.924 MSK [16566] DETAIL: parameters: $1 = '42', $2 = 'Answer: '
虽然只实现了高级查询协议的一小部分功能，但它仍然是一项非常有用的创新。
谓词锁用于确保跨具有 SERIALIZABLE 隔离级别的事务更改的一致性。对于使用 REFRESH MATERIALIZED VIEW 命令更新物化视图，它们没有实现。毕竟，此命令在其操作期间需要独占视图锁定，以防止任何并发访问异常。
但是如果使用CONCURRENTLY选项执行更新，那么在没有谓词锁的情况下，SERIALIZABLE隔离级别的事务可能会遇到写倾斜异常。已更正。
在累积统计系统中，除了表和索引访问次数的计数器外，还记录了最后一次访问的日期和时间信息。为此，已将 last_seq_scan 和 last_idx_scan 列添加到 pg_stat_all_tables，并将 last_idx_scan 列添加到 pg_stat_all_indexes。
SELECT seq_scan, last_seq_scan, idx_scan, last_idx_scan
FROM pg_stat_all_tables
WHERE relid = 'tickets'::regclass;
seq_scan | last_seq_scan | idx_scan | last_idx_scan
----------+-------------------------------+----------+-------------------------------
23 | 2022-12-07 15:17:57.261575+03 | 2 | 2022-12-05 14:59:53.564968+03
(1 row)
SELECT indexrelname, idx_scan, last_idx_scan
FROM pg_stat_all_indexes
WHERE relid = 'tickets'::regclass;
indexrelname | idx_scan | last_idx_scan
----------------------+----------+-------------------------------
tickets_pkey | 2 | 2022-12-05 14:59:53.564968+03
tickets_book_ref_idx | 0 |
(2 rows)
在没有专门的监控系统的情况下分析索引和表的使用时，它会很有用。
pg_buffercache：新函数 pg_buffercache_summary
提交： 2589434a
pg_buffercache 扩展中添加了一个函数来获取有关缓冲区缓存的摘要信息：
CREATE EXTENSION pg_buffercache;
SELECT * FROM pg_buffercache_summary()\gx
-[ RECORD 1 ]--+------------------
buffers_used | 2544
buffers_unused | 13840
buffers_dirty | 38
buffers_pinned | 0
usagecount_avg | 2.183176100628931
可以通过聚合 pg_buffercache 视图更早地获得此信息。但新功能的工作不需要缓冲区阻塞，因此成本较低，便于在监控系统中使用。
walsender 在提交进程状态**中显示数据库名称**： af205152
复制协议连接由 walsender 进程提供服务。对于物理复制或备份，此过程连接到一个实例，而不是特定的数据库。但是，当您创建逻辑复制订阅时，会在发布服务器上创建一个逻辑复制槽，并且 walsender 会连接到发布所在的数据库。
为了方便监控为逻辑复制服务的 walsender 进程，进程状态补充了数据库的名称。
16(sub)=# CREATE SUBSCRIPTION sub CONNECTION 'port=5416 user=postgres dbname=demo' PUBLICATION pub;
NOTICE: created replication slot "sub" on publisher
CREATE SUBSCRIPTION
16(pub)=# \! ps -o pid,command --ppid `head -n 1 /home/pal/pg16/data/postmaster.pid`
PID COMMAND
38514 postgres: checkpointer
38515 postgres: background writer
38517 postgres: walwriter
38518 postgres: autovacuum launcher
38519 postgres: logical replication launcher
38522 postgres: postgres demo [local] idle
38662 postgres: walsender postgres demo [local] START_REPLICATION
在本例中，可以看到walsender进程的状态中显示了数据库名称demo。
Peter Gagan正在研究主动行冻结。工作目的很明确——为了避免事务计数器循环出现问题，需要提前开始冻结行。然而，较早的冻结会生成更多的 WAL 并增加相关的开销（复制、WAL 归档）。
在这个补丁中，Peter 提出了一种更紧凑（~5x）的方法来构建与行冻结相关的 WAL 记录。
当主服务器空闲时，物理副本上的启动进程仍然每 5 秒唤醒一次，检查_promote_trigger_file 中指定的文件是否出现_。但是还有其他机制可以提升副本：pg_ctl promote 和 pg_promote 函数。为了减少此类唤醒的资源消耗，已删除_promote_trigger_file_参数。
walreceiver 进程完成了类似的工作，它每秒唤醒 10 次，检查是否到了执行某事的时间。该流程现在会预先计算下一次运行的时间。
提交：97da4824
在第 14 版中出现了向第三方表中插入记录的批处理模式。批量大小由第三方服务器或单独表级别的 batch_size 参数设置。但它仅针对 INSERT 命令实现。此补丁添加了对 COPY FROM 命令的批量粘贴支持。
让我们用不同的命令测量插入速度。为此，我们将在 postgres 数据库中创建一个空的预订表，我们将在其中插入数据。
postgres=# CREATE TABLE public.bookings (
book_ref char(6),
book_date timestamptz,
total_amount numeric(10,2)
);
在同一集群的演示数据库中，让我们为新创建的创建一个外部表。
CREATE EXTENSION postgres_fdw;
CREATE SERVER srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'postgres', batch_size '1');
CREATE USER MAPPING FOR postgres
SERVER srv
OPTIONS (user 'postgres');
CREATE FOREIGN TABLE bookings_remote (
book_ref char(6),
book_date timestamptz,
total_amount numeric(10,2)
) SERVER srv
OPTIONS (schema_name 'public', table_name 'bookings');
数据包大小在服务器级别设置并且等于 1，即 批处理模式被禁用，记录被一张一张地传输。
让我们将 bookings演示数据库表的内容上传到一个文件：
\COPY bookings TO 'bookings.txt'
现在我们将文件的内容加载到第三方表中，并启用时间测量：
\timing on
\COPY bookings_remote FROM 'bookings.txt'
COPY 2111110
Time: 57327,152 ms (00:57,327)
插入两百万行多一点花了将近一分钟的时间。将数据包大小设置为 100 并重复测量：
TRUNCATE bookings_remote;
ALTER SERVER srv OPTIONS (SET batch_size '100');
\COPY bookings_remote FROM 'bookings.txt'
COPY 2111110
Time: 8780,000 ms (00:08,780)
粘贴速度提升6倍以上！
奇怪的是，批处理模式下的 INSERT 命令甚至更快：
TRUNCATE bookings_remote;
INSERT INTO bookings_remote SELECT * FROM bookings;
INSERT 0 2111110
Time: 6669,504 ms (00:06,670)
所以插入优化的可能性还没有用尽。此外，最好教 postgres_fdw 使用批处理模式来修改和删除行，而不仅仅是读取和插入。
现代化基础设施以使用配置参数
提交： f13b2088、3057465a、 407b50f2、9c911ec0
这一系列的提交着重于优化内存存储和加快对配置参数的访问。包括用户设置。
在 Tom Lane在讨论的第一封信中给出的示例中可以最清楚地看到优化：
do $$
begin
for i in 1..10000 loop
perform set_config('foo.bar' || i::text, i::text, false);
end loop;
end $$;
在我的电脑上，在版本 15 中创建 10,000 个自定义参数需要大约 6 秒，应用补丁后，时间减少到大约 30 毫秒。
优化哈希索引
生成 提交： e09d7a12、 d09dbeb9
创建哈希索引时，值仅按篮子的数量排序。如果额外按值排序，那么后续值的插入速度会更快。
第一次提交是在 7 月，第二次是在 11 月。总的来说，哈希索引的创建速度提高了 5-15%。
MAINTAIN - 新的
提交表维护权限： 60684dd8
许多表和物化视图维护操作不受特权限制，通常仅对对象所有者和超级用户可用。
16版本出现了MAINTAIN权限，可以给物化视图和表颁发，包括系统的：
GRANT MAINTAIN ON pg_class, pg_attribute, pg_type TO alice;
\dp 命令的输出使用字符 m 来表示此权限：
\dp pg_catalog.pg_class
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
------------+----------+-------+----------------------------+-------------------+----------
pg_catalog | pg_class | table | postgres=arwdDxtm/postgres+| |
| | | =r/postgres +| |
| | | alice=m/postgres | |
这个特权有什么好处？这些包括命令的执行：ANALYZE、VACUUM（包括 VACUUM FULL）、CLUSTER、REINDEX、REFRESH MATERIALIZED VIEW 和 LOCK TABLE。
此外，还添加了 pg_maintain 预定义角色。此角色的成员资格授予对数据库中所有关系的 MAINTAIN 特权。这对于将系统维护为无权对数据库执行 DDL 和 DML 操作的普通角色很有用。
补丁的原始版本建议添加两个权限 - VACUUM 和 ANALYZE，但最终选择了具有更广泛维护功能的 MAINTAIN 权限。
SET ROLE：控制切换到另一个角色
提交： 3d14e171
此补丁延续了使用角色和权限构建工作的主题。上一篇文章讲了授予角色成员资格和继承特权。
现在我们将讨论使用 SET ROLE 命令切换到另一个角色的可能性。假设我们决定将 alice 角色包含在具有特权继承的 pg_read_all_data 中：
postgres=# GRANT pg_read_all_data TO alice WITH INHERIT TRUE;
alice 角色现在可以使用 pg_read_all_data 权限。但是没有什么能阻止 alice 切换到 pg_read_all_data 并…代表她创建对象：
postgres=# \c - alice
You are now connected to database "postgres" as user "alice".
alice=> CREATE TABLE t (id int);
CREATE TABLE
alice=> \dt t
List of relations
Schema | Name | Type | Owner
--------+------+-------+------------------
public | t | table | pg_read_all_data
事实上，有些东西阻碍了。要创建对象，pg_read_all_data 角色必须对某些模式（例如 public）具有 CREATE 特权。而且从15版本开始这个权限，伪角色public就没有了。因此，对于上面的例子，之前执行的命令是：
postgres=# GRANT CREATE ON SCHEMA public TO public;
然而，在某些情况下，切换到不同的角色可能是不可取的。如果我们使用 INHERIT TRUE 提供角色成员资格，那么为什么要切换呢？在新版本中，可以使用相同的 GRANT 命令禁用此功能：
postgres=# GRANT pg_read_all_data TO alice WITH INHERIT TRUE, SET FALSE;
现在 alice 可以使用 pg_read_all_data 权限，但无法切换到它。
alice=> SET ROLE pg_read_all_data;
ERROR: permission denied to set role "pg_read_all_data"
切换到角色的能力存储在 pg_auth_members 表的 set_option 列中，在 inherit_option 和 admin_option 旁边。
SELECT roleid::regrole, member::regrole, grantor::regrole,
admin_option, inherit_option, set_option
FROM pg_auth_members
WHERE member = 'alice'::regrole\gx
-[ RECORD 1 ]--+-----------------
roleid | pg_read_all_data
member | alice
grantor | postgres
admin_option | f
inherit_option | t
set_option | f
文件包含 pg_hba.conf 和 pg_ident.conf 中的指令
提交： a54b658c
配置文件 pg_hba.conf 和 pg_ident.conf 以及 postgresql.conf 现在支持 include、include_if_exists、include_dir 指令来包含其他文件。
pg_hba_file_rules 和 pg_ident_file_mappings 视图添加了包含文件名和规则/匹配编号的列：
SELECT * FROM pg_hba_file_rules
WHERE file_name LIKE '%hba_ident_test.conf' \gx
-[ RECORD 1 ]---------------------------------
rule_number | 1
file_name | /home/pal/pg/hba_ident_test.conf
line_number | 1
type | local
database | {all}
user_name | {alice}
address |
netmask |
auth_method | peer
options | {map=m1}
error |
SELECT * FROM pg_ident_file_mappings
WHERE file_name LIKE '%ident_test.conf' \gx
-[ RECORD 1 ]-----------------------------
map_number | 1
file_name | /home/pal/pg/ident_test.conf
line_number | 1
map_name | m1
sys_name | student
pg_username | alice
error |
可以在 pg_hba.conf 文件中为用户名和数据库名称指定正则表达式。如果这些字段中的任何一个以正斜杠 (/) 开头，则该值被视为正则表达式。
原文标题：PostgreSQL 16: Часть 3 или Коммитфест 2022-11
原文作者：pluzanov
原文链接：https://postgrespro.com/blog/pgsql/5969929
