PostgreSQL Minor Versions: Time to Update!

In this blog post we’ll look at what the newly-released PostgreSQL minor versions contain. You probably want to update your current versions and use these updates.

You might already have seen that they released the updates for supported PostgreSQL versions on November 8, 2018. PostgreSQL releases minor versions with several bug fixes and feature enhancements each quarter. An important point to note is that PostgreSQL 9.3 got its final minor version release (9.3.24) this quarter, and is no longer supported.

We always recommended that you keep your PostgreSQL databases updated to the latest minor versions. Applying a minor release might need a restart after updating the new binaries. The following is the sequence of steps you should follow to upgrade to the latest minor versions:

1. Shutdown the PostgreSQL database server
2. Install the updated binaries
3. Restart your PostgreSQL database server

Most times, you can choose to update the minor versions in a rolling fashion, in a master-slave (replication) setup. Just perform the update on one server after another, but not all-at-once. Rolling updates avoid downtime for both reads and writes simultaneously. However, we recommended that you shutdown, update and restart them all-at-once while you are performing the updates.

One of the most important fixes is a security fix: CVE-2018-16850. The bug allowed an attacker with CREATE privileges on some non-temporary schema or TRIGGER privileges on some table to create a malicious trigger that, when dumped and restored using pg_dump/pg_restore, would result in additional SQL statements being executed. This applies to PostgreSQL 10 and 11 versions.

Before proceeding further, let’s look at the list of minor versions released this quarter.

• PostgreSQL 11.1
• PostgreSQL 10.6
• PostgreSQL 9.6.11
• PostgreSQL 9.5.15
• PostgreSQL 9.4.20
• PostgreSQL 9.3.25

Now, let us look into the benefits you should see by updating your Postgres versions with the latest minor versions.

PostgreSQL 11.1

PostgreSQL 11.0 was released on October 18, 2018. You might want to look at our blog post on our first take on PostgreSQL 11. With the new minor release PostgreSQL 11.1, we get some interesting functionalities and fixes after 21 days of its previous release, as seen here. The following is a small list of fixes that you might find interesting:

• Ability to create child indexes of partition tables in another tablespace.
• NULL handling in parallel hashed multi-batch left joins.
• Fix to the strictness logic that incorrectly ignored rows of ORDER BY values those were null.
• Disable recheck_on_update that is forced off for all indexes, as it is not ready yet and requires more time.
• Prevent creation of a partition in a trigger attached to its parent table
• Disallow the pg_read_all_stats role from executing pg_stat_statements_reset() to rest the stats as it is just needed for monitoring. You must run ALTER EXTENSION pg_stat_statements UPDATE … to get this into effect.

PostgreSQL 10.6

There are some common fixes that were applied to PostgreSQL 11.1 and PostgreSQL 10.6. You can find PostgreSQL 10.6 release details here. Some of the fixes applied to PostgreSQL 10.6 are in common with other supported PostgreSQL versions, as highlighted below:

• Disallow the pg_read_all_stats role from executing pg_stat_statements_reset() to rest the stats as it is just needed for monitoring. (PostgreSQL 11.1 and 10.6)
• Avoid pushing sub-SELECTs containing window functions, LIMIT, or OFFSET to parallel workers to avoid the behavior of different workers getting different answers due to row-ordering variations. (PostgreSQL 9.6 and 10.6)
• Fixed the WAL file recycling logic that might make a standby fail to remove the WAL files that need to be removed. (PostgreSQL 9.5.15, 9.6.11 and 10.6)
• Handling of commit-timestamp tracking during recovery has been fixed to avoid recovery failures while trying to fetch the commit timestamp for a transaction that did not record it. (PostgreSQL 9.5.15, 9.6.11 and 10.6)
• Applied the fix that ensures that the background workers are stopped properly when the postmaster receives a fast-shutdown request before completing the database startup. (PostgreSQL 9.5.15, 9.6.11 and 10.6)
• Fixed unnecessary failures or slow connections when multiple target host names are used in libpq so that the DNS lookup happens one at a time but not all at once. (PostgreSQL 10.6)

Following is a list of some common fixes applied to PostgreSQL 9.6.11, PostgreSQL 9.5.15, PostgreSQL 9.4.20 and PostgreSQL 9.3.25:

• Avoid O(N^2) slowdown in regular expression match/split functions on long strings.
• Fix mis-execution of SubPlans when the outer query is being scanned backward.
• Fix failure of UPDATE/DELETE … WHERE CURRENT OF … after rewinding the referenced cursor.
• Fix EvalPlanQual to avoid crashes or wrong answers in concurrent updates, when the code contains an uncorrelated sub-SELECT inside a CASE construct.
• Memory leak in repeated SP-GiST index scans has been fixed.
• Ensure that hot standby processes use the correct WAL consistency point to prevent possible misbehavior after reaching a consistent database state during WAL replay.
• Fix possible inconsistency in pg_dump’s sorting of dissimilar object names.
• Ensure that pg_restore will schema-qualify the table name when emitting DISABLE/ENABLE TRIGGER commands when a restore is ran using a restrictive search_path.
• Fix pg_upgrade to handle event triggers in extensions correctly.
• Fix pg_upgrade’s cluster state check to work correctly on a standby server.
• Fix build problems on macOS 10.14

Now that you understand the added fixes to existing PostgreSQL versions, we recommend that you test and update your PostgreSQL databases with the new minor versions (if you haven’t already).

If you are currently running your databases on PostgreSQL 9.3.x or earlier, we recommend that you to prepare a plan to upgrade your PostgreSQL databases to the supported versions ASAP. Please subscribe to our blog posts so that you know about the various options on upgrading your PostgreSQL databases to a supported major version.