PostgreSQL 15 16 小版本更新信息小结 版本更新是不是挤牙膏

15.0

支持 SQL Merge 功能

https://www.postgresql.org/docs/release/15.0/

15.0

更细粒度的对逻辑复制进行设置包含设置指定列的功能

https://www.postgresql.org/docs/release/15.0/

15.0

针对 ZSTD 压缩的支持，比如在 pg_basebackup 中使用数据压缩的新的方式

https://www.postgresql.org/docs/release/15.0/

15.0

Json 结构化输出日志的功能

https://www.postgresql.org/docs/release/15.0/

15.0

对于内存和磁盘排序的功能

https://www.postgresql.org/docs/release/15.0/

15.0

移除了 public schema 创建 object 的权限

Change the owner of the public schema to be the new pg_database_owner role (Noah Misch)

15.0

提高内存中排序的性能并减少内存消耗

https://www.postgresql.org/docs/release/15.0/

15.0

提高在 work_mem 内存不足后的排序性能

https://www.postgresql.org/docs/release/15.0/

15.0

允许 select distinct 使用并行

https://www.postgresql.org/docs/release/15.0/

15.0

允许 full page 使用 lz4 和 zstd 的数据压缩方式

This is controlled by the wal_compression server setting.

15.0

对系统中的窗口函数进行性能提升

Improve the performance of window functions that use row_number(), rank(), dense_rank() and count() (David Rowley)

15.0

提升 copy 命令的数据写入的性能

Improve performance of psql's \copy command, by sending data in larger chunks

15.1

修复删除临时表空间时的遗漏致使磁盘空间浪费的问题

Fix failure to remove non-first segments of large tables (Tom Lane)

15.1

在 GIN 索引的快速插入路径中修复 WAL 操作的错误排序

https://www.postgresql.org/docs/release/15.1/

15.2

修复在分区表或继承树的 GENERATED 期间，计算子表中哪些 UPDATE 列需要更新的问题

https://www.postgresql.org/docs/release/15.2/

15.2

修复 merge 语句中 bug 问题

Fix MERGE's check for unreachable WHEN clauses (Dean Rasheed)

15.3

在使用 create schema 中防止命令对 search_path 中的设置进行更改

Prevent CREATE SCHEMA from defeating changes in search_path (Alexander Lakhin)

15.3

修复 MERGE 并发更新时的问题

Fix MERGE problems with concurrent updates (Dean Rasheed, Álvaro Herrera)

15.3

修复执行计划内存泄露的问题

Fix memory leak in Memoize plan execution (David Rowley)

15.3

在

https://www.postgresql.org/docs/release/15.3/

contrib/pg_walinspect 中，限制 pg_get_wal_records_info() 的内存使用

In contrib/pg_walinspect, limit memory usage of pg_get_wal_records_info() (Bharath Rupireddy)

https://www.postgresql.org/docs/release/15.3/

15.4

针对可执行脚本的安全设置，如果名称包含了引号，反斜杠，美元符号则不可以在扩展脚本中进行所有者名称的替换

避免 SQL 注入的安全风险（CVE - 2023 - 39417）

15.4

修复 BRIN 索引中空白（无行）范围和全 NULL 范围之间的混淆，以及全 NULL 摘要的不正确合并

https://www.postgresql.org/docs/release/15.4/

15.5

修复在 DISTINCT``"any"聚合函数中对未知类型参数的处理

(CVE-2023-5868)

15.5

防止 pg_signal_backend 角色向后台工作进程和自动清理进程发送信号

安全性巩固

15.5

在 BRIN interval_minmax_multi_ops 索引中避免具有极端区间值的计算溢出

https://www.postgresql.org/docs/release/15.5/

15.5

避免系统日志记录器进程的标准输入意外关闭

15.7

pg_stats_ext and pg_stats_ext_exprs安全性更新

修复安全漏洞

15.7

修复当表基于布尔列进行分区且查询具有布尔IS NOT子句时对 NULL 分区的不正确修剪（David Rowley）

A NULL value satisfies a clause such as boolcol IS NOT FALSE, so pruning away a partition containing NULLs yielded incorrect answers.

15.7

在删除孤立临时表期间避免死锁

15.7

修复 XID 状态函数中旧事务 ID 的检测

Transaction IDs more than 231 transactions in the past could be misidentified as recent, leading to misbehavior of pg_xact_status() or txid_status().

15.7

确保表的可用空间映射不会返回超出表末尾的页面

Because the freespace map isn't WAL-logged, this was possible in edge cases involving an OS crash, a replica promote, or a PITR restore. The result would be a “could not read block” error.

15.7

修复 BRIN 输出函数中的错误

https://www.postgresql.org/docs/release/15.7/

PG16

允许FULL和内部右侧OUTER哈希连接的并行化

https://www.postgresql.org/docs/release/16.0/

PG16

允许从备用服务器进行逻辑复制

PG16

允许逻辑复制订阅使用并行来处理较大的事务中的数据

PG16

允许使用新的pg_stat_io视图监测 I/O 统计信息

PG16

提高了vacuum freezing的性能

PG16

更改针对PL/pgSQL绑定游标变量的赋值规则

Previously, the string value of such variables was set to match the variable name during cursor assignment; now it will be assigned during OPEN, and will not match the variable name. To restore the previous behavior, assign the desired portal name to the cursor variable before OPEN.

PG16

createrole权限角色安全性问题，原有的权限过大的问题被修整

The role's default inheritance behavior can be overridden with the new GRANT ... WITH INHERIT clause. This allows inheritance of some roles and not others because the members' inheritance status is set at GRANT time. Previously the inheritance status of member roles was controlled only by the role's inheritance status, and changes to a role's inheritance status affected all previous and future member roles.

PG16

对ORDER BY或DISTINCT的聚合添加使用预排序数据的能力

PG16

允许将不可为空的输入作为内部关系来执行反连接,允许FULL和内部右OUTER哈希连接的并行化

https://www.postgresql.org/docs/release/16.0/

PG16

针对vauum和analyze在缓冲区中的内存的使用进行优化

The VACUUM/ANALYZE option is BUFFER_USAGE_LIMIT, and the vacuumdb option is --buffer-usage-limit. The default value is set by server variable vacuum_buffer_usage_limit, which also controls autovacuum.

PG16

autovacuum 运行中影响性能后，调节延迟参数生效性的改变

Allow autovacuum to more frequently honor changes to delay settings Rather than honor changes only at the start of each relation, honor them at the start of each block.

PG16

允许在windows10 上使用 huge pages

PG16

允许 logical decoding 在standbys上进行设置

Snapshot WAL records are required for logical slot creation but cannot be created on standbys. To avoid delays, the new function pg_log_standby_snapshot() allows creation of such records.

PG16

允许并行复制在逻辑复制上使用

The CREATE SUBSCRIPTION STREAMING option now supports parallel to enable application of large transactions by parallel workers. The number of parallel workers is controlled by the new server variable max_parallel_apply_workers_per_subscription. Wait events LogicalParallelApplyMain, LogicalParallelApplyStateChange, and LogicalApplySendData were also added. Column leader_pid was added to system view pg_stat_subscription to track parallel activity.

PG16

允许逻辑复制应用在无主键的表

Improve performance for logical replication apply without a primary key (Onder Kalaci, Amit Kapila)

PG16

提高了vacuum和vacummdb的操作的灵活性

This is accomplished by having VACUUM turn off PROCESS_MAIN or by vacuumdb using the --no-process-main option.

PG16

针对JSON的大量更新处理的函数和修改原有的问题

https://www.postgresql.org/docs/release/16.0/

PG16

pg_dump功能的更新，可以针对子表和分区表进行指定dump

The new options are --table-and-children, --exclude-table-and-children, and --exclude-table-data-and-children.

PG16

增加了LZ4和zstd压缩的方式在 pg_dump和pg_basebackup

Improve pg_dump to accept a more consistent compression syntax Options like --compress=gzip:5.

PG16

可以提供vacuumdb 排除schema和指定schema的功能

These are controlled by options --schema and --exclude-schema.

PG16

针对pg_walinspect,pg_waldump,pg_buffercache等进行功能更新

PG16.1

在各种 SQL 函数中更干净地处理无效索引

Report an error if pgstatindex(), pgstatginindex(), pgstathashindex(), or pgstattuple() is applied to an invalid index. If brin_desummarize_range(), brin_summarize_new_values(), brin_summarize_range(), or gin_clean_pending_list() is applied to an invalid index, do nothing except to report a debug-level message. Formerly these functions attempted to process the index, and might fail in strange ways depending on what the failed CREATE INDEX had left behind.

16.1

修复分区表中的批量表插入

Improper sharing of insertion state across partitions could result in failures during COPY FROM, typically manifesting as “could not read block NNNN in file XXXX: read only 0 of 8192 bytes” errors.

16.1

修复了在执行explain中可能导致崩溃的问题

Avoid crash in EXPLAIN if a parameter marked to be displayed by EXPLAIN has a NULL boot-time value (Xing Guo, Aleksander Alekseev, Tom Lane)

16.1

修复pg_dump pg_restore 部分bug

Fix pg_restore so that selective restores will include both table-level and column-level ACLs for selected tables,Fix pg_dump to dump the new run_as_owner option of subscriptions

PG16.2

避免产生错误的分区表执行计划

PG16.2

修复PlaceHolderVars中子查询输出表达式不正确的包装

This fixes incorrect results when a subquery is underneath an outer join and has an output column that laterally references something outside the outer join's scope. The output column might not appear as NULL when it should do so due to the action of the outer join.

16.2

修复了merge update工作中的一些问题尤其在分区表中的一些bug

When executing a MERGE UPDATE action on a partitioned table, if the UPDATE is turned into a DELETE and INSERT due to changing a partition key column, skip firing AFTER UPDATE ROW triggers, as well as other post-update actions such as RLS checks. These actions would typically fail, which is why a regular UPDATE doesn't do them in such cases; MERGE shouldn't either.

PG16.2

在并行哈希连接中避免请求过大的共享内存区域

pg16.2

修复了在继承树中复杂的情况下，进行alter table add column可能产生的错误问题

Fix possible failure during ALTER TABLE ADD COLUMN on a complex inheritance tree (Tender Wang)

16.2

在备用服务器中在子事务期间错误处理死元组

Prevent standby servers from incorrectly processing dead index tuples during subtransactions (Fei Changhong)

PG16.3

修复了系统安全性漏洞

Restrict visibility of pg_stats_ext and pg_stats_ext_exprs entries to the table owner (Nathan Bossart)

PG16.3

在删除孤立临时表期间避免死锁

If the session that creates a temporary table crashes without removing the table, autovacuum will eventually try to remove the orphaned table. However, an incoming session that's been assigned the same temporary namespace will do that too. If a temporary table has a dependency (such as an owned sequence) then a deadlock could result between these two cleanup attempts.

PG16.3

对vacuum的程序进行了加固避免运行中的可能产生的错误

VACUUM's computation of per-database frozen-XID values from per-relation values could get confused by a concurrent update of those values by another VACUUM.

PG16.3

这个问题也在之前整理PG12的版本中经常发现，难道这个问题在每个版本都有问题

Transaction IDs more than 231 transactions in the past could be misidentified as recent, leading to misbehavior of pg_xact_status() or txid_status().

PG16.3

避免在PG查询中查询被cancel后内存泄露

This happened only when cancelling a non-last query in a query string made with ; separators.