2023-01-23
Happy new PostgreSQL! New Year’s news on version 15.1 and predecessors
At the end of 2022, we received a series of new updates from the Postgres team that improve the quality and security of our databases. In this article we will take a closer look at the latest PostgreSQL patches, list the versions that received patches, for which this is the last official patch. We invite you to read on.
At the end of 2022, we received a series of new updates from the Postgres team that improve the quality and security of our databases. Today we will take a closer look at the latest PostgreSQL patches, list the versions that received patches, for which this is the last official patch. We invite you to read on.
The PostgreSQL engineering team continues to positively surprise with the speed of releasing fixes and patches for their product. After the release of the highly anticipated Postgres version 15 and its warm reception by users, the developers did not rest on their laurels. Just less than a month later, on November 10, 2022, we received a significant patch of fixes for various versions of the Postgres database system. The support of the PostgreSQL development team covers only the most recent versions of the database, hence the patches are available for the following versions: 15.1, 14.6, 13.9, 12.13, 11.18, i 10.23.
The best time for change
It should be emphasized that the support for Postgres version 10 has reached its end. As the developers themselves emphasize: “PostgreSQL 10 will no longer receive bug fixes and security patches.” Postgres 10.23 is therefore the final form of the version 10 product and it is recommended to upgrade to higher versions. To learn more about upgrading to a newer version of PostgreSQL, we recommend taking a look at the built-in pg_upgrade tool. The documentation is available here.
A detailed description of the fixes
Here is a list and description of the fixes that have been prepared for version 15.1. However, some of them are also provided for previous versions of PostgreSQL, including the 10.23 release.
| Change | Additional informations | Author(s) |
|---|---|---|
| Fix failure to remove non-first segments of large tables | Orphaned temporary-table files are removed during postmaster start, so the mere act of updating to 15.1 is sufficient to clear any leaked temporary-table storage. However, if you suffered any database crashes while using 15.0, and there might have been large tables dropped just before such crashes, it’s advisable to check the database directories for files named according to the pattern NNNN.NN. If there is no matching file named just NNNN (without the .NN suffix), these files should be removed manually. | Tom Lane |
| Fix handling of DEFAULT tokens that appear in a multi-row VALUES clause of an INSERT on an updatable view | This oversight could lead to “cache lookup failed for type” errors, or in older branches even to crashes. | Tom Lane |
| Disallow rules named _RETURN that are not ON SELECT | This avoids confusion between a view’s ON SELECT rule and any other rules it may have. | Tom Lane |
| Avoid failure in EXPLAIN VERBOSE for a query using SEARCH BREADTH FIRST with constant initial values | – | Tom Lane |
| Prevent use of MERGE on a partitioned table with foreign-table partitions | The case isn’t supported, and previously threw an incomprehensible error. | Álvaro Herrera |
| Fix construction of per-partition foreign key constraints while doing ALTER TABLE ATTACH PARTITION | Previously, incorrect or duplicate constraints could be constructed for the newly-added partition. | Jehan-Guillaume de Rorthais, Álvaro Herrera |
| Fix planner failure with extended statistics on partitioned or inherited tables | Some cases failed with “cache lookup failed for statistics object”. | Richard Guo, Justin Pryzby |
| Fix mis-ordering of WAL operations in fast insert path for GIN indexes | This mistake is not known to have any negative consequences within core PostgreSQL, but it did cause issues for some extensions. | Matthias van de Meent, Zhang Mingli |
| Fix bugs in logical decoding when replay starts from a point between the beginning of a transaction and the beginning of its subtransaction | These errors could lead to assertion failures in debug builds, and otherwise to memory leaks. | Masahiko Sawada, Kuroda Hayato |
| Accept interrupts in more places during logical decoding | This ameliorates problems with slow shutdown of replication workers. | Amit Kapila, Masahiko Sawada |
| Prevent attempts to replicate into a foreign-table partition in replication workers | Although partitioned tables can have foreign tables as partitions, replicating into such a partition isn’t currently supported. The logical replication worker process would crash if it was attempted. Now, an error is thrown. | Shi Yu, Tom Lane |
| Avoid crash after function syntax error in replication workers | If a syntax error occurred in a SQL-language or PL/pgSQL-language CREATE FUNCTION or DO command executed in a logical replication worker, the worker process would crash with a null pointer dereference or assertion failure. | Maxim Orlov, Anton Melnikov, Masahiko Sawada, Tom Lane |
| Avoid double call of the shutdown callback of an archiver module | – | Nathan Bossart, Bharath Rupireddy |
| Add plan-time check for attempted access to a table that has no table access method | This prevents a crash in some catalog-corruption scenarios, for example use of a view whose ON SELECT rule is missing. | Tom Lane |
| Prevent postmaster crash when shared-memory state is corrupted | The postmaster process is supposed to survive and initiate a database restart if shared memory becomes corrupted, but one bit of code was being insufficiently cautious about that. | Tom Lane |
| In libpq, handle single-row mode correctly when pipelining | The single-row flag was not reset at the correct time if pipeline mode was also active. | Denis Laxalde |
| Fix psql’s exit status when a command-line query is canceled | psql -c query would exit successfully if the query was canceled. Fix it to exit with nonzero status, as in other error cases. | Peter Eisentraut |
| Allow cross-platform tablespace relocation in pg_basebackup | Allow the remote path in –tablespace-mapping to be either a Unix-style or Windows-style absolute path, since the source server could be on a different OS than the local system. | Robert Haas |
| Fix pg_dump’s failure to dump comments attached to some CHECK constraints | – | Tom Lane |
| Fix CREATE DATABASE to allow its oid parameter to exceed 231 | This oversight prevented pg_upgrade from succeeding when the source installation contained databases with OIDs larger than that. | Tom Lane |
| In pg_stat_statements, fix access to already-freed memory | This occurred if pg_stat_statements tracked a ROLLBACK command issued via extended query protocol. In debug builds it consistently led to an assertion failure. In production builds there would often be no visible ill effect; but if the freed memory had already been reused, the likely result would be to store garbage for the query string. | zhaoqigui |
| Fix incompatibilities with LLVM 15 | – | Thomas Munro, Andres Freund |
| Allow use of __sync_lock_test_and_set() for spinlocks on any machine | This eases porting to new machine architectures, at least if you’re using a compiler that supports this GCC builtin function. | Tom Lane |
| Rename symbol REF to REF_P to avoid compile failure on recent macOS | – | Tom Lane |
| Avoid using sprintf, to avoid compile-time deprecation warnings | – | Tom Lane |
In addition to the above table of improvements with their detailed description, the naming of time zones has been corrected in accordance with tzdata of 2022. The Europe/Kiev zone has also been renamed to Europe/Kyiv. In addition, the following zones have been merged with nearby larger zones that have the same time since 1970. These are:
Antarctica/Vostok, Asia/Brunei, Asia/Kuala_Lumpur, Atlantic/Reykjavik, Europe/Amsterdam, Europe/Copenhagen, Europe/Luxembourg, Europe/Monaco, Europe/Oslo, Europe/Stockholm, Indian/Christmas, Indian/Cocos, Indian/Kerguelen, Indian/Mahe, Indian/Reunion, Pacific/Chuuk, Pacific/Funafuti, Pacific/Majuro, Pacific/Pohnpei, Pacific/Wake, Pacific/Wallis.
It should be noted that all currently used time zone names will be available as an alias to the newly assigned ones.
Summary
Having been watching the engineering team of The PostgreSQL Global Development Group for some time now, we can rest assured about the future of Postgres. With the development of new versions of the database system, we are able to administer the database in a more efficient and increasingly secure way. All those who are not satisfied with the capabilities of “pure” PosgtreSQL, we encourage you to get acquainted with the EuroDB database platform, which significantly extends the capabilities of Postgres and at the same time is fully compatible with it.