Why psql \timing ignores SET LC_NUMERIC and how to fix it

by

Summary

A developer attempted to change the decimal separator in psql timing output (e.g., changing 0,475 ms to 0.475 ms) by manually executing SET LC_NUMERIC commands within a session. Despite explicitly setting the locale to en_US.UTF-8, the output format remained unchanged. This postmortem explores why session-level locale settings often fail to influence the client-side formatting of specific interactive commands like \timing.

Root Cause

The failure occurred because of a misunderstanding of the scope of locale influence.

  • Client vs. Server Scope: The SET commands used (SET LC_NUMERIC TO...) are SQL commands that affect how the PostgreSQL server formats data returned in result sets (like dates or currency in a column).
  • psql Application Logic: The \timing command is a meta-command internal to the psql client application. The formatting of the “Time: …” string is determined by the client’s environment variables or its own internal locale configuration at startup, not by the session-level SQL variables.
  • Initialization Timing: By the time the user executes SET LC_NUMERIC, the psql process has already initialized its internal formatting logic based on the OS environment variables (like LANG or LC_ALL).

Why This Happens in Real Systems

In distributed systems and complex tooling, there is a critical distinction between Data Formatting and Tooling Formatting.

  • Data Formatting: Controlled by the database engine. If you run SELECT 1.23::numeric, the server prepares the bytes.
  • Tooling Formatting: Controlled by the driver or the CLI. The way a CLI tool (like psql, mysql, or pgcli) prints metadata, warnings, or execution timers is governed by the shell environment where the binary resides.
  • Configuration Mismatch: Engineers often assume that because they are “inside” a database session, they have total control over the environment. In reality, the client-side interface is a separate process from the database engine.

Real-World Impact

  • Parsing Failures: Automated scripts or regex-based scrapers that expect a . but receive a , will fail, leading to broken CI/CD pipelines or incorrect telemetry.
  • Developer Friction: Engineers wasting hours “fixing” database configurations when the issue is actually a local shell configuration error.
  • Observability Gaps: Inconsistent log formats across different developer machines can lead to errors in manual performance auditing.

Example or Code

To actually fix this, the change must happen before the psql process starts, by modifying the environment variables of the shell.

# This will NOT work (it changes the server's view of the session)
psql -c "SET LC_NUMERIC TO 'en_US.UTF-8'; \timing on; SELECT 1;"

# This IS the correct way (it changes the client's formatting behavior)
LC_NUMERIC=en_US.UTF-8 psql -c "\timing on; SELECT 1;"

How Senior Engineers Fix It

Senior engineers address this by looking at the entry point of the process rather than the internal state of the application.

  • Environment Injection: Instead of trying to SET variables via SQL, they wrap the execution in an environment-aware command or use a .env file.
  • Wrapper Scripts: They create standardized shell aliases or wrapper scripts to ensure all team members use consistent locale settings for debugging.
  • Standardization: They enforce UTF-8/en_US as the baseline for all production-adjacent tooling to prevent “it works on my machine” locale discrepancies.

Why Juniors Miss It

  • Scope Confusion: Juniors often treat the SQL prompt as a “God Mode” console where every setting affects everything, failing to realize psql is a client application communicating with a server.
  • Misinterpreting Documentation: They see LC_NUMERIC in PostgreSQL documentation and assume it applies to all text output, not realizing it applies specifically to data types being processed by the engine.
  • Lack of OS Awareness: They overlook the role of the Operating System environment in shaping the behavior of CLI tools.

Leave a Comment