# How the `VersionAwareQueries` Class Ensures PostgreSQL Version Compatibility

> Ensure PostgreSQL version compatibility with VersionAwareQueries. Safely execute SQL by detecting server capabilities and assembling dynamic queries that avoid unavailable features.

- Repository: [JungJungIn/mcp-postgresql-ops](https://github.com/call518/mcp-postgresql-ops)
- Tags: internals
- Published: 2026-02-26

---

**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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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.

```python
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:

```python
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`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/version_compat.py):

```python

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

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

```

```python

# 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

```

```python

# 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`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/version_compat.py), consumed by [`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) and orchestrated through [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`.