PostgreSQL 9.6.2 repairs concurrent index construction

An index built while a table remains writable has to account for updates that overlap its construction. PostgreSQL's February 9 maintenance release fixes a race in that accounting. When CREATE INDEX CONCURRENTLY indexed a previously unindexed column, concurrent transactions could leave incorrect index entries. Installing the fixed server prevents the faulty construction, but existing affected indexes need separate attention.

That distinction makes this update more consequential than its version number suggests. The announcement covers PostgreSQL 9.6.2 and corresponding updates for every supported branch. It recommends applying the update at the next scheduled downtime and calls out index corruption alongside write-ahead-log replay defects. These are changes to the reliability of operations administrators already depend on.

I default to PostgreSQL for relational work partly because its schema operations have documented operational boundaries. Concurrent index construction is one of the cases that needs particular care. This release describes a failure within that operation and states what an administrator must repair after replacing the binaries.

A successful build can still need replacement

The 9.6.2 release notes credit Pavan Deolasee and Tom Lane with the concurrent index fix. For the race to occur, the new index must depend on a column that was not previously indexed, and transactions must update rows while the command runs. An affected index can contain incorrect entries even though the command has finished.

An index is a stored representation of information derived from table rows. Correcting the code that constructs that representation does not itself reconstruct every index already stored on disk. The release therefore separates software installation from data repair. Where the race may have occurred, rebuilding the affected index after updating is the most reliable remedy.

The announcement gives a replacement sequence that preserves availability of the existing index while constructing the new one. With application-specific table and index names substituted, its example is:

CREATE INDEX CONCURRENTLY new_index_name ON table_name (column_name);
DROP INDEX CONCURRENTLY old_index_name;
ALTER INDEX new_index_name RENAME TO old_index_name;

The order matters because the old index remains present during construction of its replacement. The new server binaries must already be installed so the replacement does not encounter the same bug. This procedure also temporarily stores both indexes. The announcement explicitly notes the extra disk requirement, which can determine whether this approach is suitable for a particular installation.

The example illustrates a simple index replacement. It is not a complete description of every application's schema dependencies. Its useful operational point is that updating PostgreSQL and repairing a potentially affected index are distinct actions, with different costs and different completion conditions.

Vacuum must account for catalogue readers

Another correction concerns the special snapshot PostgreSQL uses for catalogue scans. The release notes say backends failed to include this snapshot when advertising their oldest xmin. A concurrent vacuum could then prune data that the catalogue scan still needed. The observed failure could be a transient message such as cache lookup failed for relation 1255.

The mechanism explains why such a message need not mean somebody dropped the relation named in the error. The catalogue reader still needed a version of catalogue data, but the information governing retention did not represent that reader correctly. Vacuum acted on an incomplete account of what remained visible. The fix makes that special snapshot participate in the retention calculation.

This is a useful qualification to the ordinary description of MVCC as a way to let readers and writers proceed concurrently. Old versions can only be removed safely when the server knows which readers still depend on them. A special internal reader has the same underlying requirement, even if its snapshot follows a different code path from an application's query.

I value PostgreSQL's explicit explanation here. An intermittent catalogue error can otherwise invite changes to application SQL that do not address the cause. Naming the snapshot and the missing xmin accounting gives the failure a concrete relationship to vacuum.

Recovery has to reproduce the intended state

Two write-ahead-log fixes address different forms of recovery correctness. For BRIN indexes, the WAL record for a reverse-map page was wrong when an index tuple moved to another page. Replaying that record could leave the corresponding part of the index unusable until it was recomputed. The error concerned what recovery reconstructed, even though the initiating operation happened during normal execution.

Unlogged tables expose a separate subtlety. PostgreSQL must WAL-log creation of their initialization fork even when wal_level is minimal. Before this fix, it could skip that record. After a crash, the server could consequently fail to reset the unlogged table to an empty state correctly.

The word unlogged can obscure this requirement. The release notes show that an unlogged table still needs recovery metadata to establish its intended post-crash state. Logging that initialization information supports the table's reset behavior. It does not turn the table into an ordinarily logged table.

These defects also explain why a maintenance update can matter without changing application syntax. The application may issue exactly the same statements after updating, while index construction, pruning, and recovery obey their existing contracts more reliably.

Applying the update has a bounded scope

The release contains more than 75 fixes, including corrections to parallel queries and backup utilities. One parallel-query fix prevents a crash when fewer workers are available during a rescan. Another makes planner cardinality estimates for parallel joins represent the rows each worker is expected to see. Those entries concern different stages of query processing and should not be collapsed into a general claim that parallel queries are faster.

For installations already running 9.6, the release notes require no dump and restore. The announcement describes shutting down PostgreSQL and updating the binaries, with additional instructions for installations that skipped earlier maintenance updates. The concurrent-index issue remains an explicit exception to treating binary replacement as the entire job. A server running fixed code can still retain an index built incorrectly before the update.