umzzil nng

Posted on Jul 26 • Originally published at oraerror.com

PostgreSQL HV014 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL HV014: fdw_too_many_handles — Causes, Fixes & Prevention

What Is HV014?

The PostgreSQL error HV014 (fdw_too_many_handles) occurs when a Foreign Data Wrapper (FDW) exceeds the maximum number of handles it is allowed to open simultaneously. Each FDW connection or cursor internally allocates a handle to manage communication with the external data source, and when these handles are not properly released or too many concurrent sessions use FDW simultaneously, this error is triggered. It can affect any FDW implementation including postgres_fdw, oracle_fdw, and ODBC-based wrappers.

Top 3 Causes

1. FDW Connection Handle Leaks

Handles are created each time an FDW query opens a connection or cursor. If transactions are not properly committed or rolled back, or if cursors are left open, handles accumulate over time without being released.

-- Check for long-running or idle FDW-related sessions
SELECT pid, usename, state, query_start, state_change, left(query, 80) AS query_snippet
FROM pg_stat_activity
WHERE query ILIKE '%foreign%' OR query ILIKE '%fdw%'
ORDER BY query_start ASC;

-- Terminate idle FDW connections older than 10 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '10 minutes';

2. Too Many Concurrent FDW Sessions

Without proper connection pooling, each application thread or session opens its own FDW handle. As concurrent users grow, so does handle consumption, quickly hitting the FDW limit.

-- Check how many connections are actively using FDW servers
SELECT usename, COUNT(*) AS session_count, state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, state
ORDER BY session_count DESC;

-- Disconnect all cached postgres_fdw connections to reset handles
SELECT postgres_fdw_disconnect_all();

3. Insufficient Handle Limits in FDW Server Configuration

Some FDW drivers impose their own handle pool limits. A mismatch between max_connections on the PostgreSQL instance and the FDW server's allowed connections, or an undersized driver-level handle pool, can trigger HV014.

-- Review current FDW server configurations
SELECT s.srvname, s.srvoptions, f.fdwname
FROM pg_foreign_server s
JOIN pg_foreign_data_wrapper f ON f.oid = s.srvfdw;

-- Optimize postgres_fdw server options to reduce handle pressure
ALTER SERVER my_foreign_server
OPTIONS (SET fetch_size '500');

-- Enable connection reuse to reduce handle churn
ALTER USER MAPPING FOR current_user
SERVER my_foreign_server
OPTIONS (ADD keep_connections 'on');

Quick Fix Solutions

Disconnect and reset FDW handles immediately:

-- Disconnect a specific FDW server's cached connections
SELECT postgres_fdw_disconnect('my_foreign_server');

-- Reset all FDW server connections at once
SELECT postgres_fdw_disconnect_all();

Always use explicit transactions and close cursors:

BEGIN;
DECLARE my_fdw_cursor CURSOR FOR
    SELECT order_id, total FROM foreign_orders WHERE status = 'PENDING';

FETCH 100 FROM my_fdw_cursor;
-- process rows...
CLOSE my_fdw_cursor;  -- Always explicitly close cursors
COMMIT;

Reduce per-query handle lifetime with fetch_size tuning:

-- Reduce the number of rows fetched per round trip
ALTER FOREIGN TABLE foreign_orders OPTIONS (SET fetch_size '200');

Prevention Tips

1. Automate idle connection cleanup with pg_cron:

-- Schedule automatic cleanup of idle connections every 5 minutes
SELECT cron.schedule(
    'fdw-handle-cleanup',
    '*/5 * * * *',
    $$
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE state = 'idle'
      AND state_change < NOW() - INTERVAL '5 minutes'
      AND backend_type = 'client backend';
    $$
);

2. Deploy connection pooling and enforce FDW query best practices:

Use PgBouncer in transaction pooling mode to cap the number of real PostgreSQL sessions. Always select only the columns you need from foreign tables, keep FDW queries inside short transactions, and avoid using SELECT * over foreign tables in high-concurrency environments.

-- Example: Targeted query to minimize handle hold time
SELECT order_id, order_date, total_amount
FROM foreign_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'COMPLETED';

Related Errors

HV000 – fdw_error: Generic FDW error, often a precursor to HV014.
HV001 – fdw_out_of_memory: Memory exhaustion during FDW operations, can co-occur with handle leaks.
HV00B – fdw_invalid_handle: Accessing an already-released or invalid handle.
53300 – too_many_connections: Instance-level connection limit exceeded, commonly occurs alongside FDW handle exhaustion.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community