Technical Analysis: psql Tab Completion Missing IO Option for EXPLAIN

Core Problem

When commit 681daed9316 introduced EXPLAIN (IO) as a new boolean option in PostgreSQL, the corresponding psql tab-completion logic in tab-complete.in.c was not updated. This is a common class of oversight in PostgreSQL development: a backend feature lands but the client-side tooling (psql) doesn't receive matching updates in the same commit.

The result is a degraded interactive experience in two specific ways:

1. Option keyword omission: When a user types EXPLAIN ( and presses <Tab>, the completion list enumerates known EXPLAIN options (ANALYZE, BUFFERS, COSTS, FORMAT, SETTINGS, SUMMARY, TIMING, VERBOSE, WAL, etc.) but does not include IO.
2. Boolean branch omission: Even if a user manually types EXPLAIN (IO and presses <Tab>, psql does not offer ON / OFF completions, because IO is not listed in the pattern that matches boolean-valued EXPLAIN options.

Architectural Context

How psql Tab Completion Works

psql's tab completion is driven by a large, pattern-matching framework in src/bin/psql/tab-complete.in.c. This file is preprocessed to generate tab-complete.c. The completion logic uses a series of Matches / TailMatches / HeadMatches macro calls to detect partial SQL input and return appropriate completion candidates.

For EXPLAIN, there are typically two relevant sections:

1. Option keyword list: A static array or inline list of strings that enumerates all valid EXPLAIN (...) option keywords. When the parser detects EXPLAIN ( as the current input prefix, it offers these keywords as completions.
2. Boolean option branch: A pattern like TailMatches("EXPLAIN", "(", "ANALYZE|BUFFERS|COSTS|IO|SETTINGS|...", ...) that, when an option keyword is recognized as boolean-typed, offers ON and OFF as the next completions. Non-boolean options like FORMAT have their own branches (offering TEXT, JSON, XML, YAML).

The fix is mechanically simple — add "IO" to both lists — but it matters because tab completion is a primary discoverability mechanism for PostgreSQL features. Users who rely on psql's interactive completion to explore new options would have no indication that IO exists.

The EXPLAIN (IO) Feature

The IO option (added in commit 681daed9316) instructs EXPLAIN to include I/O statistics in the query plan output. This is a boolean option (ON/OFF), similar to BUFFERS, WAL, and TIMING. It provides visibility into the actual I/O operations (reads, writes, extends, fsyncs, hits) performed during query execution, broken down by node. This is architecturally significant as it exposes the I/O subsystem's activity at the executor level, complementing the buffer-level statistics provided by BUFFERS.

Proposed Solution

The patch modifies src/bin/psql/tab-complete.in.c in two places:

1. Adds "IO" to the list of EXPLAIN option keywords offered on initial tab completion after EXPLAIN (.
2. Adds IO to the alternation pattern for boolean EXPLAIN options so that EXPLAIN (IO <Tab> correctly offers ON / OFF.

This is a minimal, low-risk fix with no behavioral changes outside of psql's interactive completion.

Quality and Completeness Assessment

The patch is straightforward and correct in scope. The only potential concern (not raised in the thread) would be whether it should also handle the lowercase variant io, though psql's tab completion is typically case-insensitive in its matching. Additionally, one might check whether \pset or other psql meta-commands need any corresponding updates, but IO is purely an EXPLAIN option and doesn't interact with other psql settings.

Community Reception

The thread is brief — only two messages — indicating this is an uncontroversial, clearly correct fix. Zhenwei Shang confirmed the approach (updating both the keyword list and the boolean branch) is the right fix. Given the trivial nature of the change, this is likely to be committed quickly, possibly by any committer reviewing the psql completion code or by the committer who originally landed 681daed9316.

This type of fix is often backpatched to the same branch where the original feature was introduced, since it's a pure client-side completion fix with no risk.