How the `VersionAwareQueries` Class Ensures PostgreSQL Version Compatibility

The VersionAwareQueries class guarantees safe SQL execution across PostgreSQL versions by detecting server capabilities at runtime and dynamically assembling queries that avoid referencing unavailable views, columns, or features.

The call518/mcp-postgresql-ops repository provides database operation tools that must function reliably across PostgreSQL releases from version 10 through 16+. The VersionAwareQueries class, defined in src/mcp_postgresql_ops/version_compat.py, serves as the central abstraction layer that shields the application from version-specific schema differences.

Centralized Version Detection with Caching

Every query builder starts by invoking get_postgresql_version (line 87 in version_compat.py). This async function executes SELECT version() against the target database, parses the major, minor, and patch numbers from the result string, and stores the PostgreSQLVersion object in _cached_version. This caching mechanism prevents redundant database round-trips during a single session while ensuring all subsequent operations reference the same version metadata.

version = await get_postgresql_version(database)

# version.major, version.minor, version.patch available for logic branching

Feature-Flag Architecture via PostgreSQLVersion

The PostgreSQLVersion dataclass (lines 14–73) encapsulates version knowledge through boolean properties that act as feature flags. Each property encodes the specific version threshold where a PostgreSQL feature became available:

• has_pg_stat_io: Returns True when self.major >= 16 (introduced in PostgreSQL 16)
• has_checkpointer_split: Returns True for PostgreSQL 15+ (when checkpointer statistics moved to a separate view)
• has_enhanced_wal_receiver: Indicates availability of enhanced WAL receiver statistics

Query builders test these flags rather than hardcoding version comparisons, creating self-documenting conditional logic:

if version.has_pg_stat_io:
    # Use modern pg_stat_io view (PostgreSQL 16+)

else:
    # Fall back to pg_statio_* views (PostgreSQL 10-15)

Conditional SQL Assembly Utilities

The class delegates mechanical query construction to two specialized helpers:

get_compatible_column_list (lines 53–86) generates comma-separated SELECT lists. When a column does not exist in the detected version, it inserts NULL::text AS column_name placeholders, ensuring the query parser succeeds even when referencing newer columns like leader_pid (PostgreSQL 14+) or query_id (PostgreSQL 13+) on older servers.

get_version_appropriate_query (lines 88–112) accepts a dictionary mapping PostgreSQLVersion objects to query strings and returns the most specific variant for the detected version, falling back to a default query if no exact match exists.

Dynamic Query Building in Practice

Static methods within VersionAwareQueries combine these primitives to deliver version-safe SQL:

• get_bgwriter_checkpointer_stats: Returns a unified pg_stat_bgwriter query for PostgreSQL 10–14, but switches to separate pg_stat_checkpointer and pg_stat_bgwriter views when version.has_checkpointer_split indicates PostgreSQL 15+.
• get_io_statistics: Selects the pg_stat_io view only when version.has_pg_stat_io confirms PostgreSQL 16 or newer; otherwise, constructs compatible queries against legacy statistics views.
• get_activity_with_leader_info: Uses get_compatible_column_list to conditionally include leader_pid (PostgreSQL 14+) and query_id (PostgreSQL 13+), substituting NULL placeholders for these columns on older versions.

Practical Implementation Examples

The following patterns demonstrate how to leverage the version-aware infrastructure in src/mcp_postgresql_ops/version_compat.py:

# Automatically select the correct I/O statistics view for any PG version

stats_sql = await VersionAwareQueries.get_io_statistics(database="production")

# Build activity query that gracefully degrades across versions

activity_sql = await VersionAwareQueries.get_activity_with_leader_info(
    database="analytics"
)

# PostgreSQL 14+: includes leader_pid column

# PostgreSQL 13: includes query_id but NULL for leader_pid

# PostgreSQL 12: NULL placeholders for both columns

# Select the optimal query variant from a version map

from mcp_postgresql_ops.version_compat import PostgreSQLVersion

version_specific_queries = {
    PostgreSQLVersion(15): "SELECT * FROM pg_stat_checkpointer",
    PostgreSQLVersion(12): "SELECT * FROM pg_stat_bgwriter"
}

optimal_query = await get_version_appropriate_query(
    queries_by_version=version_specific_queries,
    fallback_query="SELECT * FROM pg_stat_bgwriter",
    database="mydb"
)

Summary

• VersionAwareQueries centralizes PostgreSQL compatibility logic in src/mcp_postgresql_ops/version_compat.py, consumed by functions.py and orchestrated through mcp_main.py.
• Version detection occurs via get_postgresql_version, which caches the PostgreSQLVersion object to minimize database overhead.
• Feature flags (e.g., has_pg_stat_io, has_checkpointer_split) encapsulate version thresholds as readable boolean properties.
• Column compatibility is handled by get_compatible_column_list, which injects NULL::type placeholders for unavailable columns rather than omitting them.
• Query selection logic in get_version_appropriate_query routes execution to the most capable SQL variant supported by the connected server.

Frequently Asked Questions

How does VersionAwareQueries detect the PostgreSQL server version?

The detection flow begins with get_postgresql_version, an async function that queries SELECT version(), parses the semantic version components, and stores the result in the module-level _cached_version variable. All subsequent version-aware operations reference this cached object to ensure consistency throughout the database session.

What happens when querying columns that exist only in newer PostgreSQL versions?

The get_compatible_column_list utility (lines 53–86) constructs SELECT statements that substitute NULL::text AS column_name for any column unavailable in the detected version. This approach allows the same query structure to execute on PostgreSQL 10 through 16+ without raising "column does not exist" errors.

Which specific PostgreSQL features trigger different query paths?

Key version thresholds implemented in the PostgreSQLVersion class include PostgreSQL 16+ for the pg_stat_io view (checked via has_pg_stat_io), PostgreSQL 15+ for the split checkpointer statistics (checked via has_checkpointer_split), PostgreSQL 14+ for the leader_pid column in activity views, and PostgreSQL 13+ for the query_id column. Each threshold determines whether the query uses modern views or includes NULL placeholders for missing columns.

Where is the version compatibility infrastructure located in the repository?

All version-aware logic resides in src/mcp_postgresql_ops/version_compat.py, which defines the PostgreSQLVersion dataclass, the VersionAwareQueries static method collection, and the utility functions get_postgresql_version, get_compatible_column_list, and get_version_appropriate_query.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}