PostgreSQL 14.4 Released!

Posted on 2022-06-16 by PostgreSQL Global Development Group
PostgreSQL Project

The PostgreSQL Global Development Group has released PostgreSQL 14.4 to fix an issue that could cause silent data corruption when using the CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY commands. Please see the following section for information on how to detect and correct silent data corruption in your indexes.

This release also fixes over 15 bugs since PostgreSQL 14.3 was released in May. For the full list of changes, please review the release notes.

This release is only for PostgreSQL 14. The PostgreSQL Global Development Group will make a scheduled update release on August 11, 2022 for all supported versions of PostgreSQL (10 - 14).

Detect and Fix "CREATE INDEX CONCURRENTLY" / "REINDEX CONCURRENTLY" Corruption

PostgreSQL 14.4 fixes an issue with CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY that could cause silent data corruption of indexes. Prior to the fix, CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY could build indexes that would have missing entries, causing SELECT queries that used the index to not find certain rows. This issue may not have corrupted your indexes, but if you are unsure, we advise you to reindex using the instructions below.

You can detect if a B-tree index (the default index type) has data corruption using the pg_amcheck command with the --heapallindexed flag. For example:

pg_amcheck --heapallindexed database

If pg_amcheck detects corruption or if you ran CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY on any other index type (e.g. GiST, GIN, etc.), please follow the instructions below.

Once you upgrade your system to PostgreSQL 14.4, you can fix any silent data corruption using REINDEX CONCURRENTLY. For example, if you have an index named elephant_idx that has data corruption, you can run the following command on PostgreSQL 14.4 to fix it:

REINDEX CONCURRENTLY elephant_idx;

You can use the reindexdb command to reindex all indexes across your cluster. reindexdb also has a --jobs flag that lets you run reindex operations in parallel. For example, to reindex your entire PostgreSQL cluster using <N> parallel jobs, you can run the following command:

reindexdb --all --concurrently --jobs <N>

Bug Fixes and Improvements

This update fixes over 15 bugs that were reported in the last several months. The issues listed below affect PostgreSQL 14.

Included in this release:

  • Several fixes for query plan memoization.
  • Fix queries in which a "whole-row variable" references the result of a function that returns a domain over composite type.
  • Fix "variable not found in subplan target list" planner error using a sub-SELECT that is referenced in a GROUPING function.
  • Fix error checking in COPY FROM when the database encoding is SQL_ASCII but the client encoding is a multi-byte encoding.
  • Report implicitly-created operator families (generated by CREATE OPERATOR CLASS) to event triggers.
  • Prevent triggering wal_receiver_timeout on a standby during logical replication of large transactions.
  • Remove incorrect TLS private key file ownership check in libpq.
  • Prevent crash after server connection loss in pg_amcheck.

For the full list of changes available, please review the release notes.

Updating

All PostgreSQL update releases are cumulative. As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shutdown PostgreSQL and update its binaries.

However, if you ran CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY on PostgreSQL 14, you may need to take additional steps. Please review the "Detect and Fix CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY Corruption" section for more details.

Users who have skipped one or more update releases may need to run additional, post-update steps; please see the release notes for earlier versions for details.

For more details, please see the release notes.

PostgreSQL 10 EOL Notice

PostgreSQL 10 will stop receiving fixes on November 10, 2022. If you are running PostgreSQL 10 in a production environment, we suggest that you make plans to upgrade to a newer, supported version of PostgreSQL. Please see our versioning policy for more information.

Links