How the MCP Server Automatically Adapts Queries for PostgreSQL Versions 12–17

The MCP server detects the PostgreSQL version at runtime using get_postgresql_version() and builds version-aware SQL statements through feature flags, dynamic column selection, and version-specific query templates to ensure seamless compatibility across PostgreSQL 12 through 17.

The call518/mcp-postgresql-ops repository implements a robust compatibility layer that allows a single MCP (Model Context Protocol) server to operate against any PostgreSQL release from version 12 to 17. By automatically detecting server capabilities and adjusting SQL generation accordingly, the tool eliminates manual version management while maintaining consistent output formats across all supported versions.

Runtime Version Detection and Caching

The adaptation process begins with precise version identification. The get_postgresql_version() function in src/mcp_postgresql_ops/version_compat.py (lines 87–128) executes SELECT version() against the connected database and parses the result into a structured PostgreSQLVersion object.

This object stores major, minor, and patch version numbers and exposes boolean feature helpers such as:

• has_pg_stat_io – Available in PostgreSQL 16+
• has_checkpointer_split – Available in PostgreSQL 14+
• has_enhanced_wal_receiver – Available in PostgreSQL 13+
• has_replication_slot_wal_status – Available in PostgreSQL 13+

To minimize overhead, the parsed version is cached in the module-level variable _cached_version. Subsequent calls return the cached object unless force_refresh=True is passed, triggering a fresh detection cycle.

Dynamic Column Selection for Schema Compatibility

When querying system catalogs that vary between releases, the server uses get_compatible_column_list() (lines 153–185) to construct SELECT statements that maintain identical result schemas regardless of the underlying PostgreSQL version.

The function accepts a list of all possible columns and a mapping of version-specific requirements. It preserves columns that exist in all versions, includes conditional columns only when the version requirement is satisfied, and injects NULL::text AS column placeholders for missing columns.

from .version_compat import get_compatible_column_list, PostgreSQLVersion

all_cols = ["pid", "datname", "usename", "leader_pid", "query_id"]
version_specific = {
    "leader_pid": PostgreSQLVersion(14),   # added in PG 14

    "query_id":   PostgreSQLVersion(13),   # added in PG 13

}
cols_sql = await get_compatible_column_list(
    "pg_stat_activity",
    all_cols,
    version_specific,
)

# Returns: "pid, datname, usename, leader_pid, query_id" on PG ≥14

# Returns: "pid, datname, usename, NULL::text AS leader_pid, query_id" on PG 13

This guarantees that application code receives a predictable column structure without version-specific branching logic.

Version-Specific Query Templates

The VersionAwareQueries class (lines 214–246) encapsulates static async methods for every high-level database operation, including background writer statistics, I/O metrics, replication status, and WAL receiver monitoring. Each method follows a consistent pattern:

1. Calls await get_postgresql_version() to obtain the version object
2. Checks the relevant feature flag (has_checkpointer_split, has_pg_stat_io, etc.)
3. Returns a different SQL string based on the detected capabilities
4. Falls back to legacy views or NULL placeholders when features are unavailable

For example, the replication slots query (lines 308–357) conditionally includes wal_status and safe_wal_size columns only when version.has_replication_slot_wal_status returns true (PostgreSQL 13+). On older versions, it selects the same logical columns using NULL::text AS wal_status placeholders to maintain schema consistency.

from .version_compat import VersionAwareQueries

# Automatically uses pg_stat_io on PG 16+, falls back to pg_statio_all_tables on older versions

io_sql = await VersionAwareQueries.get_io_statistics()
rows = await execute_query(io_sql)

Centralized Query Selection and Execution

For operations requiring simple query variants, get_version_appropriate_query() (lines 88–111) selects among multiple pre-written SQL templates using a mapping of PostgreSQLVersion to query strings. The execute_version_aware_query() helper (lines 80–99) combines selection and execution into a single asynchronous call, further reducing boilerplate in the tool implementation.

Integration in the MCP Tool Layer

High-level MCP tools in src/mcp_postgresql_ops/mcp_main.py (lines 65–71) invoke these version-aware builders without concerning themselves with underlying PostgreSQL versions:

slots_query = await VersionAwareQueries.get_replication_slots_query()
repl_slots = await execute_query(slots_query)

receiver_query = await VersionAwareQueries.get_wal_receiver_query()
wal_receiver = await execute_query(receiver_query)

The rest of the codebase remains completely isolated from version-specific logic. The compatibility layer guarantees consistent output formats, allowing operators to upgrade PostgreSQL from version 12 through 17 without modifying client code or tool configurations.

Summary

• Runtime detection: get_postgresql_version() parses SELECT version() output into a cached PostgreSQLVersion object with feature flags for capabilities like has_pg_stat_io and has_checkpointer_split.
• Schema normalization: get_compatible_column_list() dynamically builds SELECT statements with NULL placeholders for missing columns, ensuring uniform result sets across versions 12–17.
• Template selection: The VersionAwareQueries class provides static methods that return version-specific SQL strings, automatically falling back to legacy views when newer features are unavailable.
• Clean abstraction: High-level tools in mcp_main.py consume these utilities transparently, requiring no version-specific logic in the application layer.

Frequently Asked Questions

How does the MCP server detect which PostgreSQL version is running?

The server executes SELECT version() via get_postgresql_version() in src/mcp_postgresql_ops/version_compat.py, parses the result into a PostgreSQLVersion object containing major, minor, and patch components, and caches this in the module-level _cached_version variable to avoid repeated queries on subsequent calls.

What happens when a query references columns that don't exist in older PostgreSQL versions?

The get_compatible_column_list() function (lines 153–185) automatically replaces unavailable columns with NULL::text AS column placeholders. This ensures the result set schema remains identical whether running against PostgreSQL 12 or PostgreSQL 17, preventing application errors due to missing columns.

Which specific PostgreSQL features does the version compatibility layer handle?

According to the source code, the system specifically manages pg_stat_io statistics (16+), split checkpointer statistics (14+), enhanced WAL receiver information (13+), and replication slot wal_status and safe_wal_size columns (13+), falling back to legacy system views or NULL values when these features are absent.

Can the version detection cache be refreshed without restarting the server?

Yes. While the parsed version is stored in _cached_version for performance, passing force_refresh=True to get_postgresql_version() bypasses the cache and re-executes SELECT version() against the database, allowing the server to adapt to PostgreSQL upgrades or connection pool changes dynamically.

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