Add psql tab completion support for FOR PORTION OF

First seen: 2026-04-15 03:52:36+00:00 · Messages: 3 · Participants: 2

Latest Update

2026-05-14 · claude-opus-4-6

Technical Analysis: Add psql Tab Completion Support for FOR PORTION OF

Core Problem

PostgreSQL's temporal tables feature introduces the FOR PORTION OF clause, which is used in UPDATE and DELETE statements to operate on specific time ranges of temporal data. For example:

UPDATE my_temporal_table FOR PORTION OF valid_period FROM '2024-01-01' TO '2024-06-01' SET salary = 50000;
DELETE FROM my_temporal_table FOR PORTION OF valid_period FROM '2024-01-01' TO '2024-06-01';

The psql interactive terminal provides tab completion as a developer convenience feature, allowing users to discover and auto-complete SQL syntax as they type. However, the FOR PORTION OF clause — a relatively new and syntactically unusual construct — lacked any tab completion support. This means users working with temporal tables interactively had no discoverability or typing assistance for this clause, despite it being a multi-keyword construct (FOR PORTION OF <column> FROM <value> TO <value>) that is particularly easy to misremember.

Architectural Context

Tab completion in psql is implemented in src/bin/psql/tab-complete.c, which is one of the largest and most complex files in the PostgreSQL frontend codebase. It works by pattern-matching against the tokens already typed on the current line and offering completions based on grammatical rules. The completion logic is essentially a hand-coded, heuristic parser — it does not use the server's actual grammar — and operates via a long chain of Matches/TailMatches/HeadMatches macros that check trailing token sequences.

Adding support for FOR PORTION OF requires inserting new matching rules that:

  1. After UPDATE <table>, offer FOR PORTION OF as a completion option (alongside SET, AS, etc.).
  2. After FOR PORTION OF, offer column names (specifically, the temporal period column).
  3. After FOR PORTION OF <column>, offer FROM.
  4. After FROM <value>, offer TO.
  5. After TO <value>, offer the next expected keyword (SET for UPDATE, WHERE/USING/AS for DELETE, etc.).

The same pattern applies for DELETE FROM <table> FOR PORTION OF ....

Proposed Solution and What Was Committed

Kiran submitted a patch adding tab completion rules covering the FOR PORTION OF clause for both UPDATE and DELETE statements, along with corresponding tests (psql tab completion tests live in src/bin/psql/t/).

The Defect Identified by Peter Eisentraut

Peter Eisentraut tested the patch and found that the final rule — completing the keyword after ... FOR PORTION OF <col> FROM <val> TO <val> — did not work. Specifically:

update pg_class for portion of oid from 1 to 2 <tab>

...offered no completions, when it should have offered SET (for UPDATE) or contextually appropriate keywords for DELETE.

This is a subtle but common problem in psql's tab completion system. The matching macros count backward from the end of the input, and with a long multi-token prefix like FOR PORTION OF <col> FROM <val> TO <val>, it's easy to get the token count wrong, or to have the match conflict with other existing rules. The heuristic nature of the system means there's no formal grammar validation — rules can silently fail if they don't match the exact token pattern at completion time.

Resolution

Rather than delay the entire patch to debug the broken rule, Peter Eisentraut took the pragmatic approach of committing the patch with the non-working rule removed. This means the committed version provides tab completion for:

But it does not complete the terminal keyword (SET, etc.) after the full FOR PORTION OF ... FROM ... TO ... clause. This is a minor gap — users would need to type SET manually — but all the harder-to-remember parts of the syntax are covered.

Key Technical Insights

The tab completion system in psql is fundamentally a best-effort heuristic system. It cannot perfectly model SQL grammar, and multi-token clauses like FOR PORTION OF expose the limitations of pattern-matching against token sequences. The longer the prefix pattern that must be matched, the more fragile the rule becomes. This is a well-known maintenance burden in tab-complete.c.

The fact that the test infrastructure exists for tab completion (added in relatively recent PostgreSQL versions) is notable — it allows patch authors to submit testable completion rules, though in this case the broken rule apparently wasn't caught by the submitted tests, suggesting the test coverage may not have exercised the exact token sequence that fails interactively.

Significance

While this is a small usability patch rather than an architectural change, it matters because temporal tables are a major SQL:2011 feature being progressively added to PostgreSQL. Making the interactive experience smooth for temporal SQL encourages adoption and helps users learn the syntax. Tab completion patches like this are often a signal that a feature has matured enough to warrant full tooling integration.