PostgreSQL 14 小版本分析，有那个版本不建议使用

文摘 2024-07-22 06:02 天津

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题，有需求都可以加群群内有数据库行业大咖，加群请联系 liuaustin3 ，（共2320人左右 1 + 2 + 3 + 4 +5+6 +7）新人分配到6群。

常用的PostgreSQL的小版本已经总结了12，15，16，这次我们总结一下PostgreSQL 14版本中的小版本，PostgreSQL 14版本相对于PG15来说在一些功能的更新上稍显不足，但这个版本正好在PG的爆发期的一个时间点上发布，使用这个版本的群体不少，并且这个版本目前也更新到了14.12这个版本，所以需要分析一下。

注：PG14.4 修复严重的BUG,使用PG14需要使用PG14.4及以后的版本

注：PG14.6 修复了vacuum特殊情况下导致的系统重启的可能性

PG14

数据库版本	更新要点/bug fixed	注释/链接
PG14	存储过程返回数据参数的修改	https://www.postgresql.org/docs/release/14.0/
PG14	范围类型扩展，添加非连续数据范围
PG14	针对并行，高并发工作负载，分区表逻辑复制，vacuum做出大量性能改进
PG14	提高BTREE索引更新的效率，减少索引膨胀	B-tree index updates are managed more efficiently, reducing index bloat.
PG14	对vacuum在遇到transaction ID wraparound的情况更加积极的进行vacuum 工作
PG14	提高收集统计信息的准确性，为更复杂的查询提供更好的查询计划	Extended statistics can now be collected on expressions, allowing better planning results for complex queries.
PG14	添加预定义角色pg_read_all_data 和 pg_write_all_data
PG14	允许真空回收未使用的尾部堆行指针所占用的空间允许真空在最小锁定索引操作期间更积极地删除死行	https://www.postgresql.org/docs/release/14.0/
PG14	添加跳过 TOAST 表吸尘的能力	VACUUM now has a PROCESS_TOAST option which can be set to false to disable TOAST processing, and vacuumdb has a --no-process-toast option.
PG14	允许 B 树索引添加以删除过期的索引条目来防止页分裂	This is particularly helpful for reducing index bloat on tables whose indexed columns are frequently updated.
pg14	提高MVCC快照实现执行的速度	This also improves performance when there are many idle sessions.
PG14	添加在 TOAST 数据上使用LZ4 压缩的能力	This can be set at the column level, or set as a default via server parameter default_toast_compression. The server must be compiled with --with-lz4 to support this feature. The default setting is still pglz.
PG14	添加众多函数对许多操作的进度进行展示
PG14	添加idle超时的函数	Add server parameter idle_session_timeout to close idle sessions
PG14	修改一些参数的默认值	Change checkpoint_completion_target default to 0.9

PG14.1

数据库版本	更新要点/bug fixed	注释/链接
PG14.1	并行的VACUUM如果表中至少有两个索引大于min_parallel_index_scan_size的阈值，而有些索引低于该阈值，则无法处理这些低于阈值的索引。这可能会导致这些索引损坏，因为它们仍包含对VACUUM删除的任何堆项的引用；后续使用此类索引的查询很可能会返回不应返回的行。此问题不会影响自动清理（autovacuum），因为它不使用并行清理。但是，对于具有适当索引大小组合的任何手动清理的表，建议重新索引。	Ensure that parallel VACUUM doesn't miss any indexes (Peter Geoghegan, Masahiko Sawada)
PG14.1	修复在主服务器发送以部分 WAL 记录结尾的 WAL 段后崩溃的情况下的物理复制	https://www.postgresql.org/docs/release/14.1/
pg14.1	修复reindex concurrently命令执行的问题	Fix REINDEX CONCURRENTLY to preserve operator class parameters that were attached to the target index
PG14.1	避免仅使用 MCV 统计信息来估算列值范围，因为在某些情况下这可能不准确

PG14.2

数据库版本	更新要点/bug fixed	注释/链接
PG14.2	严重的错误，在对toast表进行reindex concurrently操作会导致索引损坏	If applied to a TOAST table or TOAST table's index, REINDEX CONCURRENTLY tended to produce a corrupted index. This happened because sessions updating TOAST entries released their ROW EXCLUSIVE locks immediately, rather than holding them until transaction commit as all other updates do. The fix is to make TOAST updates hold the table lock according to the normal rule. Any existing corrupted indexes can be repaired by reindexing again.
PG14.2	在页面修剪期间，修复导致HOT链损坏的情况发生	Fix corruption of HOT chains when a RECENTLY_DEAD tuple changes state to fully DEAD during page pruning
PG14.2	修复了当数据库一致性恰好在 WAL 页面边界上达成时，WAL 重放失败的问题
PG14.2	修复了物理复制中的XID插入顺序错误的问题	Fix startup of a physical replica to tolerate transaction ID wraparound
PG14.2	在逻辑复制中子表数据可能进行重复传输的问题	In logical replication, avoid double transmission of a child table's data
PG14.2	修复了事务最后一个子事务的提交时间戳可能丢失的问题	Fix possible loss of the commit timestamp for the last subtransaction of a transaction
PG14.2	确保在检查点期间对 pg_logical/mappings 子目录进行 fsync，避免在系统崩溃后丢失逻辑重写状态文件	Be sure to fsync the pg_logical/mappings subdirectory during checkpoints
PG14.2	在 ALTER TABLE ADD PRIMARY KEY USING INDEX 过程中，正确更新缓存的表状态，修复了并发会话未能正确更新表主键状态的问题	Correctly update cached table state during ALTER TABLE ADD PRIMARY KEY USING INDEX
评估4.2	修复了更新表达式索引时的内存泄漏问题	Fix memory leak when updating expression indexes

PG14.3

数据库版本	更新要点/bug fixed	注释/链接
PG14.3	修复删除分区索引时出现死锁故障的风险	Ensure that the required table and index locks are taken in the standard order (parents before children, tables before indexes). The previous coding for DROP INDEX did it differently, and so could deadlock against concurrent queries taking these locks in the standard order.
PG14.3	修复在一个TRUNCATE命令与检查点重叠之后崩溃恢复中可能出现的问题	TRUNCATE must ensure that the table's disk file is truncated before the checkpoint is allowed to complete. Otherwise, replay starting from that checkpoint might find unexpected data in the supposedly-removed pages, possibly causing replay failure.
PG14.3	确保 contrib/pageinspect 函数能够处理全零页面

PG14.4

数据库版本	更新要点/bug fixed	注释/链接
PG14.4	防止使用CONCURRENTLY选项创建或重建的索引可能出现损坏	An optimization added in v14 caused CREATE INDEX ... CONCURRENTLY and REINDEX ... CONCURRENTLY to sometimes miss indexing rows that were updated during the index build. Revert that optimization. It is recommended that any indexes made with the CONCURRENTLY option be rebuilt after installing this update. (Alternatively, rebuild them without CONCURRENTLY.
PG14.4	修复 Memoize 计划的错误成本估算	This mistake could lead to Memoize being used when it isn't really the best plan, or to very long executor startup times due to initializing an overly-large hash table for a Memoize node.
PG14.4	修复在拉起在分组函数中被引用的子SELECT时出现的“子计划目标列表中未找到变量”规划器错误

PG14.5

数据库版本	更新要点/bug fixed	注释/链接
PG14.5	修复备用服务器上创建数据库 WAL 记录的重放	Standby servers may encounter missing tablespace directories when replaying database-creation WAL records. Prior to this patch, a standby would fail to recover in such a case; however, such directories could be legitimately missing. Create the tablespace (as a plain directory), then check that it has been dropped again once replay reaches a consistent state.
PG14.5	修复create index的权限问题	The fix for CVE-2022-1552 caused CREATE INDEX to apply the table owner's permissions while performing lookups of operator classes and other objects, where formerly the calling user's permissions were used. This broke dump/restore scenarios, because pg_dump issues CREATE INDEX before re-granting permissions.
PG14.5	允许在进行统计信息收集的时候，取消 analyze操作	In some scenarios with high statistics targets, it was possible to spend many seconds in an un-cancellable sort operation.
PG14.5	确保 pg_stop_backup() 正确清理会话状态
PG14.5	在 psql 的\watch命令中，使用 Control-C 取消后回显一个换行符	This prevents libedit (and possibly also libreadline) from becoming confused about which column the cursor is in.
PG14.5	修复 contrib/postgres_fdw 以检测发送异步数据获取查询失败的情况

PG14.6

数据库版本	更新要点/bug fixed	注释/链接
PG14.6	vacuum和update产生冲突导致系统重启（比较罕见）	If a concurrent VACUUM sets the all-visible flag bit in a page that UPDATE or DELETE is in process of modifying, the updating command needs to clear that bit again; but some code paths failed to do so, ending in a PANIC exit and database restart.
PG14.6	防止standby升主后导致WAL损坏	When a PostgreSQL instance performing archive recovery (but not using standby mode) is promoted, and the last WAL segment that it attempted to read ended in a partial record, the instance would write an invalid WAL segment on the new timeline.

PG14.7

数据库版本	更新要点/bug fixed	注释/链接
PG14.7	允许一个带有递归……循环的公用表表达式（CTE）访问其输出列	A reference to the SET column from within the CTE would fail with “cache lookup failed for type 0”.
PG14.7	修复具有不确定性排序规则的字符串哈希中的内存泄漏	Fix memory leak in hashing strings with nondeterministic collations (Jeff Davis)
PG14.7	修复DROP DATABASE和逻辑复制工作进程之间的死锁	This was caused by an ill-advised choice to block interrupts while creating a logical replication slot in the worker. In version 15 that could lead to an undetected deadlock. In version 14, no deadlock has been observed, but it's still a bad idea to block interrupts while waiting for network I/O.
PG14.7	在复制连接尝试失败后清理 libpq 连接对象	The previous coding leaked the connection object. In background code paths that's pretty harmless because the calling process will give up and exit. But in commands such as CREATE SUBSCRIPTION, such a failure resulted in a small session-lifespan memory leak.
PG14.7	在热备用服务器中，减少对主服务器上已知处于活动状态的 XID 跟踪的处理工作量	Insufficiently-aggressive cleanup of the KnownAssignedXids array could lead to poor performance, particularly when max_connections is set to a large value on the standby.
PG14.7	修复 psql 的\sf和\ef命令，以处理具有 SQL 标准函数体的 SQL 语言函数

PG14.8

数据库版本	更新要点/bug fixed	注释/链接
PG14.8	防止CREATE SCHEMA破坏search_path中原有的值	Within a CREATE SCHEMA command, objects in the prevailing search_path, as well as those in the newly-created schema, would be visible even within a called function or script that attempted to set a secure search_path. This could allow any user having permission to create a schema to hijack the privileges of a security definer function or extension script.
PG14.8	避免在create schema 中省略schema 名导致系统崩溃	The SQL standard allows writing CREATE SCHEMA AUTHORIZATION owner_name, with the schema name defaulting to owner_name. However some code paths expected the schema name to be present and would fail.
PG14.8	修复由于 vacuum_defer_cleanup_age 大于当前 64 位 xid 导致的数据损坏	In v14 and later with non-default settings of vacuum_defer_cleanup_age, it was possible to compute a very large vacuum cleanup horizon xid, leading to vacuum removing rows that are still live. v12 and v13 have a lesser form of the same problem affecting only GiST indexes, which could lead to index pages getting recycled too early.
PG14.8	修复 pg_dump ，以使在枚举类型列上进行哈希分区的分区表能够成功恢复	Since the hash codes for enum values depend on the OIDs assigned to the enum, they are typically different after a dump and restore, meaning that rows often need to go into a different partition than they were in originally. Users can work around that by specifying the --load-via-partition-root option; but since there is very little chance of success without that, teach pg_dump to apply it automatically to such tables.

PG14.9

数据库版本	更新要点/bug fixed	注释/链接
PG14.9	当DROP DATABASE被中断时，避免留下损坏的数据库	If DROP DATABASE was interrupted after it had already begun taking irreversible steps, the target database remained accessible (because the removal of its pg_database row would roll back), but it would have corrupt contents. Fix by marking the database as inaccessible before we begin to perform irreversible operations. A failure after that will leave the database still partially present, but nothing can be done with it except to issue another DROP DATABASE.
PG14.9	修复在所有分区都已附加后将分区索引标记为有效时可能出现的故障	Such an index will now be ignored, and a new child index created instead.
PG14.9	修复带有来自外部嵌套循环的包含参数的内侧哈希键的哈希连接	When rescanning the join after the values of such Params have changed, we must rebuild the hash table, but neglected to do so. This could result in missing join output rows.
PG14.9	允许btree index索引有问题的情况下继续进行vacuum操作	Allow VACUUM to continue after detecting certain types of b-tree index corruption
PG14.9	修复 contrib/pageinspect 的 gist_page_items() 函数，使其在存在包含索引列时能够正常工作

PG14.10

数据库版本	更新要点/bug fixed	注释/链接
PG14.10	防止pg_signal_backend 角色向后台工作进程和自动清理进程发送信号	The documentation says that pg_signal_backend cannot issue signals to superuser-owned processes. It was able to signal these background processes, though, because they advertise a role OID of zero. Treat that as indicating superuser ownership. The security implications of cancelling one of these process types are fairly small so far as the core code goes (we'll just start another one), but extensions might add background workers that are more vulnerable.
PG4.10	处理前端程序中 pg_control 的损坏读取	On some file systems, reading pg_control may not be an atomic action when the server concurrently writes that file. This is detectable via a bad CRC. Retry a few times to see if the file becomes valid before we report error.
PG4.10	修复后台写入器，使其向统计计数器报告其进行的任何 WAL 写入
评估4.10	DDL 命令，例如将已内联到 CALL 参数中的函数进行替换，可能会产生重新规划已被 PL/pgSQL 缓存的 CALL 的需求。之前这种情况并未发生，从而导致行为异常或诸如“缓存查找失败”之类的奇怪错误。
PG14.10	在读取预写日志（WAL）时将内存不足故障视为致命错误	Previously this would be treated as a bogus-data condition, leading to the conclusion that we'd reached the end of WAL, which is incorrect and could lead to inconsistent WAL replay.

PG14.11

数据库版本	更新要点/bug fixed	注释/链接
PG4.11	执行即时编译内联时修复内存泄漏	There have been multiple reports of backend processes suffering out-of-memory conditions after sufficiently many JIT compilations. This fix should resolve that.
PG14.11	避免生成不正确的分区链接	Some uncommon situations involving lateral references could create incorrect plans. Affected queries could produce wrong answers, or odd failures such as “variable not found in subplan target list”, or executor crashes.
PG14.11	避免逻辑复制订阅时产生死锁的情况	Fix deadlock between a logical replication apply worker, its tablesync worker, and a session process trying to alter the subscription (Shlok Kyal)

PG14.12

数据库版本	更新要点/bug fixed	注释/链接
PG14.12	在检查每个关系的冻结 XID 值时避免互相竞争	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.
PG14.12	修整insert into 多个values值插入中的错误	Such cases would either fail with surprising complaints about mismatched datatypes, or insert unexpected coercions that could lead to odd results.
PG14.12	避免在移除孤立临时表时发生死锁	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.
PG14.12	在Memoize中避免释放仍在使用的数据	In production builds this error frequently didn't cause any problems, as the freed data would most likely not get overwritten before it was used.