PostgreSQL HV021 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error HV021: FDW Inconsistent Descriptor Information

The HV021 error in PostgreSQL signals that the Foreign Data Wrapper (FDW) detected a mismatch between the local foreign table's column descriptor and the actual structure of the remote data source. This typically occurs when the remote table schema has changed after the foreign table was originally defined, or when column types were incorrectly specified during foreign table creation. Understanding and resolving this error quickly is essential for maintaining reliable cross-database data pipelines.

Top 3 Causes

1. Remote Table Schema Changed After Foreign Table Creation

The most common cause is that someone altered the remote table (added, dropped, or modified columns) without updating the corresponding local foreign table definition.

-- Check your current foreign table column definitions
SELECT attname AS column_name,
       atttypid::regtype AS data_type,
       attnum AS position
FROM pg_attribute
WHERE attrelid = 'public.orders_foreign'::regclass
  AND attnum > 0
  AND NOT attisdropped
ORDER BY attnum;

-- Re-sync using IMPORT FOREIGN SCHEMA (recommended approach)
DROP FOREIGN TABLE IF EXISTS public.orders_foreign;

IMPORT FOREIGN SCHEMA public
    LIMIT TO (orders)
    FROM SERVER remote_pg_server
    INTO public;

2. Column Type Mismatch in Foreign Table Definition

Defining a foreign table column with a different data type than the actual remote column causes the FDW to fail when building the result descriptor. For instance, mapping a remote BIGINT to a local INTEGER, or a remote TEXT to a VARCHAR(50) that cannot accommodate the actual data.

-- Fix column type mismatch with ALTER FOREIGN TABLE
ALTER FOREIGN TABLE public.orders_foreign
    ALTER COLUMN order_id TYPE BIGINT;

ALTER FOREIGN TABLE public.orders_foreign
    ALTER COLUMN total_amount TYPE NUMERIC(15, 2);

-- Add a missing column that exists on the remote side
ALTER FOREIGN TABLE public.orders_foreign
    ADD COLUMN updated_at TIMESTAMP WITH TIME ZONE;

-- Remove a column that no longer exists remotely
ALTER FOREIGN TABLE public.orders_foreign
    DROP COLUMN IF EXISTS old_status;

3. Outdated or Buggy FDW Extension Version

Third-party FDW extensions (e.g., oracle_fdw, jdbc_fdw) may have bugs or compatibility issues with newer PostgreSQL major versions, causing improper descriptor handling.

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

-- Update the FDW extension
ALTER EXTENSION postgres_fdw UPDATE;

-- For third-party FDWs: reinstall after OS-level package upgrade
DROP EXTENSION IF EXISTS oracle_fdw CASCADE;
CREATE EXTENSION oracle_fdw;

-- Recreate server and user mapping after reinstall
CREATE SERVER oracle_server
    FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (dbserver '//oracle-host:1521/ORCL');

CREATE USER MAPPING FOR current_user
    SERVER oracle_server
    OPTIONS (user 'ora_user', password 'ora_pass');

Quick Fix Solutions

If you need a fast resolution without full investigation, use IMPORT FOREIGN SCHEMA to automatically synchronize your foreign table definitions with the remote schema:

-- Drop and reimport all affected foreign tables at once
DROP FOREIGN TABLE IF EXISTS public.orders_foreign;
DROP FOREIGN TABLE IF EXISTS public.customers_foreign;

IMPORT FOREIGN SCHEMA public
    LIMIT TO (orders, customers)
    FROM SERVER remote_pg_server
    INTO public;

-- Verify the fix by running a simple test query
SELECT COUNT(*) FROM public.orders_foreign LIMIT 1;

Prevention Tips

Automate schema drift detection. Run a periodic comparison between your local foreign table definitions and the actual remote schema. Integrate this check into your CI/CD pipeline so any remote schema change triggers an alert before it causes a production incident.

-- Quick schema drift check using dblink
SELECT
    COALESCE(l.col, r.col) AS column_name,
    l.dtype AS local_type,
    r.dtype AS remote_type,
    CASE
        WHEN l.col IS NULL THEN 'Missing locally'
        WHEN r.col IS NULL THEN 'Missing remotely'
        WHEN l.dtype <> r.dtype THEN 'Type mismatch'
        ELSE 'OK'
    END AS status
FROM (
    SELECT attname AS col, atttypid::regtype::TEXT AS dtype
    FROM pg_attribute
    WHERE attrelid = 'public.orders_foreign'::regclass
      AND attnum > 0 AND NOT attisdropped
) l
FULL OUTER JOIN (
    SELECT col, dtype FROM dblink(
        'host=remote dbname=mydb user=myuser password=mypass',
        $$ SELECT column_name, data_type
           FROM information_schema.columns
           WHERE table_schema='public' AND table_name='orders' $$
    ) AS r(col TEXT, dtype TEXT)
) r ON l.col = r.col
WHERE status <> 'OK';

Always validate foreign tables in staging before deploying schema changes. Before applying any schema change to a remote database that is referenced by FDW foreign tables, run a full regression test against your foreign table queries in a staging environment. Pair this with IMPORT FOREIGN SCHEMA as your standard re-sync mechanism after every remote schema migration.

Related Errors

Error Code	Name	Relation to HV021
`HV000`	`fdw_error`	Generic FDW error parent class
`HV002`	`fdw_column_name_not_found`	Column name missing on remote side
`HV090`	`fdw_invalid_string_length_or_buffer_length`	Buffer overflow from type size mismatch
`HV00R`	`fdw_unable_to_establish_connection`	Connection-level FDW failure

📖 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