DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL HV021 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL HV021: fdw inconsistent descriptor information

The HV021 error in PostgreSQL occurs when a Foreign Data Wrapper (FDW) detects a mismatch between the local Foreign Table's column descriptor and the actual schema of the remote data source. In simple terms, PostgreSQL expected one set of columns (names, types, or order), but the remote server returned something different. This error is common in environments where remote database schemas evolve independently from the local Foreign Table definitions.

Top 3 Causes

1. Remote Table Schema Changed Without Updating Foreign Table

The most common cause. When a column is added, dropped, or its type is changed on the remote server, the local Foreign Table definition does not auto-sync.

-- Check current local Foreign Table definition
SELECT column_name, data_type, ordinal_position
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'remote_orders'
ORDER BY ordinal_position;

-- Drop and recreate the Foreign Table to match remote schema
DROP FOREIGN TABLE IF EXISTS public.remote_orders;

CREATE FOREIGN TABLE public.remote_orders (
    order_id     BIGINT NOT NULL,
    customer_id  BIGINT NOT NULL,
    order_date   TIMESTAMP WITHOUT TIME ZONE,
    status       VARCHAR(50),
    total_amount NUMERIC(18, 4),
    discount     NUMERIC(5, 2)   -- newly added column on remote
)
SERVER remote_pg_server
OPTIONS (schema_name 'public', table_name 'orders');
Enter fullscreen mode Exit fullscreen mode

2. Column Order or Data Type Mismatch in Manual Definition

FDW is sensitive to column ordering and type compatibility. If you manually define a Foreign Table with columns in a different order or with slightly different types than the remote table, HV021 can be triggered.

-- Use IMPORT FOREIGN SCHEMA to avoid manual errors
DROP SCHEMA IF EXISTS remote_schema CASCADE;
CREATE SCHEMA remote_schema;

-- Automatically import the remote schema (postgres_fdw)
IMPORT FOREIGN SCHEMA public
    LIMIT TO (orders, customers)
    FROM SERVER remote_pg_server
    INTO remote_schema;

-- Verify imported structure
SELECT table_name, column_name, data_type, ordinal_position
FROM information_schema.columns
WHERE table_schema = 'remote_schema'
ORDER BY table_name, ordinal_position;
Enter fullscreen mode Exit fullscreen mode

3. FDW Extension Version Incompatibility

After a major PostgreSQL version upgrade, FDW extensions may not be recompiled or updated, causing descriptor metadata to be misread.

-- Check installed FDW extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name LIKE '%fdw%';

-- Update the extension if a newer version is available
ALTER EXTENSION postgres_fdw UPDATE;

-- If needed, reinstall the extension
DROP EXTENSION postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;

-- Recreate server and user mapping after reinstall
CREATE SERVER remote_pg_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'db.example.com', port '5432', dbname 'mydb');

CREATE USER MAPPING FOR CURRENT_USER
    SERVER remote_pg_server
    OPTIONS (user 'fdw_user', password 'secret');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Step 1: Test the connection first
SELECT * FROM public.remote_orders LIMIT 1;

-- Step 2: Use EXPLAIN VERBOSE to inspect FDW behavior
EXPLAIN (VERBOSE, ANALYZE)
SELECT * FROM public.remote_orders LIMIT 5;

-- Step 3: Alter mismatched column types directly
ALTER FOREIGN TABLE public.remote_orders
    ALTER COLUMN total_amount TYPE NUMERIC(18, 4);

-- Step 4: Add a missing column
ALTER FOREIGN TABLE public.remote_orders
    ADD COLUMN new_field TEXT OPTIONS (column_name 'new_field');

-- Step 5: Full reimport as last resort
DROP SCHEMA IF EXISTS remote_schema CASCADE;
CREATE SCHEMA remote_schema;
IMPORT FOREIGN SCHEMA public FROM SERVER remote_pg_server INTO remote_schema;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Automate schema sync with IMPORT FOREIGN SCHEMA — Schedule a nightly job using pg_cron to drop and reimport foreign schemas. This ensures your local definitions always mirror the remote structure without manual intervention. 
-- Schedule nightly schema resync with pg_cron
SELECT cron.schedule(
    'nightly-fdw-sync',
    '0 3 * * *',
    $$
    DROP SCHEMA IF EXISTS remote_schema CASCADE;
    CREATE SCHEMA remote_schema;
    IMPORT FOREIGN SCHEMA public
        FROM SERVER remote_pg_server
        INTO remote_schema;
    $$
);
Enter fullscreen mode Exit fullscreen mode
  1. Enforce DDL change management across teams — Any DDL change on the remote server should trigger a corresponding update to the local Foreign Table. Add a schema-validation step to your CI/CD pipeline or deployment checklist that compares remote and local column definitions before releasing to production. Always keep FDW extensions up to date and recompile them after every major PostgreSQL upgrade.

Related Errors

  • HV000 (fdw_error): Generic FDW error, often a parent class of HV021.
  • HV005 (fdw_column_name_not_found): Raised when a column name in the Foreign Table cannot be located on the remote side.
  • HV002 (fdw_dynamic_parameter_value_needed): Missing required parameter during FDW query execution.
  • 08006 (connection_failure): Remote server connection failure, which may precede descriptor errors.

📖 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.

Top comments (0)