最近整理了 MySQL 的 8.0.0 到 8.0.37 的版本中主要的更新内容要点和官方的链接的位置，PG 在版本上功能上，更新的速度相对 MySQL 有过之而无不及，本期我们也过一过 PG 从 PG 12 到 PG 16 中小版本的更新的功能和 Bug Fixed。这里我们从 PG12 开始的每个小版本一直到 PG16 的每个小版本中的更新的 release note 的记录中挑拣重要的进行列表。PG15--PG16 中各个小版本的内容更新较多，可能由于时间的原因和个人的能力原因，忽略掉您认为重要的更新，您可以告诉我将其进行完善。
15.0
版本号 | 更新要点/bug fixed | 链接/注释 |
|---|---|---|
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
版本号 | 更新要点/bug fixed | 链接/注释 |
|---|---|---|
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
版本号 | 更新要点/bug fixed | 链接/注释 |
|---|---|---|
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
版本号 | 更新要点/bug fixed | 链接/注释 |
|---|---|---|
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
版本号 | 更新要点/bug fixed | 链接/注释 |
|---|---|---|
15.4 | 针对可执行脚本的安全设置，如果名称包含了引号，反斜杠，美元符号则不可以在扩展脚本中进行所有者名称的替换 | 避免 SQL 注入的安全风险（CVE - 2023 - 39417） |
15.4 | 修复 BRIN 索引中空白（无行）范围和全 NULL 范围之间的混淆，以及全 NULL 摘要的不正确合并 | https://www.postgresql.org/docs/release/15.4/ |
15.5
版本号 | 更新要点/bug fixed | 链接/注释 |
|---|---|---|
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.6 跳过
15.7
版本号 | 版本更新功能 | 网页连接/注释 |
|---|---|---|
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/ |
PostgreSQL 是当前最新的PG数据库版本，此版本中有很多更新的功能，下面我们整理一下，同时需要注意一些PG16对于PGSQL的变化，防止升级后对原有的一些语句执行或语句的正确性有影响
PG 16
版本号 | 版本号更新功能 | 网页连接/注释 |
|---|---|---|
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等进行功能更新 |
PG 16.1
版本号 | 版本号更新功能 | 网页连接/注释 |
|---|---|---|
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 |
PG 16.2
版本号 | 版本号更新功能 | 网页连接/注释 |
|---|---|---|
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) |
PG 16.3
版本号 | 版本号更新功能 | 网页连接/注释 |
|---|---|---|
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. |
