PostgreSQL 17 JSON_TABLE Memory Quota & Path Execution Guardrail

--- description: PostgreSQL 17 JSON_TABLE Memory Quota & Path Execution Guardrail - Enforces strict query planning limits, plan-node memory boundaries, and structural constraints on PostgreSQL 17 JSON_TABLE queries within OLTP transaction paths. Unbounded nested path projections over arbitrary-depth JSONB columns bypass default work_mem quotas, triggering memory ballooning, buffer cache churn, and cascading node OOM events. globs: src/**/*.repository.ts, src/**/*.dao.ts, src/**/*.service.ts, src/database/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-json-table-memory-guardrail" --- # PostgreSQL 17 JSON_TABLE Memory Quota & Path Execution Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Nested JSON_TABLE Projections Without Explicit work_mem Isolation and Path-Depth Constraints ## System Constraints You are a Principal Engineer enforcing PostgreSQL 17 JSON_TABLE Memory Quota & Path Execution Guardrails. Mandate strict memory caps, strict path expressions (JSONPath lax mode disabled where unbounded arrays occur), projection limits via explicit nested PATH clauses, and localized work_mem/statement_timeout ceilings inside transactional boundaries. ## Strict Guidelines - Engineers MUST ALWAYS isolate SQL queries executing PostgreSQL 17 JSON_TABLE inside a scoped transaction or helper that bounds 'work_mem' and 'statement_timeout' locally via SET LOCAL. - Engineers MUST NOT use greedy recursive or wildcard JSON path operators (such as '$..*' or unbounded lax array splats '$[*]') directly inside high-concurrency OLTP queries without concrete root filtering and strict pagination (LIMIT/OFFSET). - Engineers MUST declare explicit data types with explicit lengths (e.g., VARCHAR(n), NUMERIC(p,s)) in the COLUMNS clause instead of defaulting to unconstrained TEXT or generic JSONB scalar extraction. - Queries MUST specify error-handling behaviors using ON EMPTY and ON ERROR clauses (such as EMPTY ON ERROR) to prevent unhandled plan termination and resource leaks in long-running batch scans. - Services MUST NEVER permit unsanitized user-supplied JSONPath expressions directly into the PASSING or PATH clauses of JSON_TABLE; all expressions must be statically validated compile-time constants. ## Bad vs Good Implementations ### Unbounded Lax JSON_TABLE Projection vs. Isolated Guarded Query #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executes an unbounded nested path traversal on the global connection work_mem without memory bounds, error fallbacks, or data type sizing. High concurrency spikes cause massive backend process memory expansion and OOM kills. ```typescript import { Pool } from 'pg'; export async function getNestedCustomerEvents(pool: Pool, customerId: string): Promise<any[]> { // Anti-Pattern: Executing unbounded JSON_TABLE with lax path wildcard in OLTP connection pool. // Can traverse megabytes of nested payloads without memory ceilings, consuming connection work_mem. const query = ` SELECT jt.* FROM customer_audits ca, JSON_TABLE( ca.payload, '$.events[*]' COLUMNS ( event_id TEXT PATH '$.id', event_type TEXT PATH '$.type', NESTED PATH '$.attributes[*]' COLUMNS ( attr_key TEXT PATH '$.key', attr_val TEXT PATH '$.value' ) ) ) AS jt WHERE ca.customer_id = $1; `; const result = await pool.query(query, [customerId]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Wraps execution in a transactional block with SET LOCAL work_mem and statement_timeout. Constrains the JSONPath using 'strict' mode with bounded slice ranges ('[0 to 99]'), sizes column outputs (VARCHAR(64)), and gracefully isolates query errors using 'EMPTY ON ERROR'. ```typescript import { Pool, PoolClient } from 'pg'; interface CustomerEventRow { event_id: string; event_type: string; attr_key: string | null; attr_val: string | null; } export async function getNestedCustomerEvents( pool: Pool, customerId: string, limit = 100 ): Promise<CustomerEventRow[]> { const client: PoolClient = await pool.connect(); try { await client.query('BEGIN READ ONLY'); // Guardrail: Enforce deterministic memory ceilings and strict timeouts for this operation await client.query("SET LOCAL work_mem = '4MB'"); await client.query("SET LOCAL statement_timeout = '2000'"); const query = ` SELECT jt.event_id, jt.event_type, jt.attr_key, jt.attr_val FROM customer_audits ca, JSON_TABLE( ca.payload, 'strict $.events[0 to 99]' PASSING ca.payload COLUMNS ( event_id VARCHAR(64) PATH '$.id' ERROR ON ERROR, event_type VARCHAR(32) PATH '$.type' DEFAULT 'UNKNOWN' ON EMPTY, NESTED PATH 'strict $.attributes[0 to 19]' COLUMNS ( attr_key VARCHAR(64) PATH '$.key', attr_val VARCHAR(255) PATH '$.value' ) ) EMPTY ON ERROR ) AS jt WHERE ca.customer_id = $1 LIMIT $2; `; const result = await client.query<CustomerEventRow>(query, [customerId, limit]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK').catch(() => {}); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 JSON_TABLE Memory Quota & Path Execution Guardrail

PostgreSQL 17 JSON_TABLE Memory Quota & Path Execution Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict query planning limits, plan-node memory boundaries, and structural constraints on PostgreSQL 17 JSON_TABLE queries within OLTP transaction paths. Unbounded nested path projections over arbitrary-depth JSONB columns bypass default work_mem quotas, triggering memory ballooning, buffer cache churn, and cascading node OOM events. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Unbounded Nested JSON_TABLE Projections Without Explicit work_mem Isolation and Path-Depth Constraints Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Unbounded Nested JSON_TABLE Projections Without Explicit work_mem Isolation and Path-Depth Constraints	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS isolate SQL queries executing PostgreSQL 17 JSON_TABLE inside a scoped transaction or helper that bounds 'work_mem' and 'statement_timeout' locally via SET LOCAL.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.