PostgreSQL HV004 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error HV004: fdw_invalid_data_type — Causes, Fixes & Prevention

PostgreSQL error HV004 (fdw_invalid_data_type) occurs when a Foreign Data Wrapper (FDW) encounters a column data type that it cannot process or that is incompatible between the remote data source and the local PostgreSQL definition. This error typically surfaces during CREATE FOREIGN TABLE execution or at query runtime when PostgreSQL attempts to fetch and convert data from an external server such as Oracle, MySQL, or a flat file source.

Top 3 Causes

1. Unsupported or User-Defined Types in Foreign Table Definition

Most FDW drivers do not support PostgreSQL user-defined types, domain types, or complex types like ENUM. Declaring such types directly in a CREATE FOREIGN TABLE statement will trigger HV004 immediately.

-- ❌ Problematic: user-defined type in foreign table
CREATE FOREIGN TABLE orders_ext (
    order_id  INTEGER,
    status    order_status_enum,   -- custom type → triggers HV004
    amount    NUMERIC(38, 10)
)
SERVER mysql_server
OPTIONS (dbname 'shop', table_name 'orders');

-- ✅ Fix: use base types instead
CREATE FOREIGN TABLE orders_ext (
    order_id  INTEGER,
    status    TEXT,                -- safe base type
    amount    NUMERIC(15, 4)       -- adjusted precision
)
SERVER mysql_server
OPTIONS (dbname 'shop', table_name 'orders');

2. Type Mapping Mismatch Between Remote and Local Schemas

Different databases use similar type names with different semantics. Oracle's DATE includes time components, while PostgreSQL's date does not. MySQL's TINYINT(1) is not a native BOOLEAN in PostgreSQL's FDW layer. These mismatches cause HV004 at data retrieval time.

-- ❌ Wrong mapping: Oracle DATE mapped to PostgreSQL date
CREATE FOREIGN TABLE employees_ext (
    emp_id    INTEGER,
    hire_date DATE           -- Oracle DATE has time → mismatch
)
SERVER oracle_server
OPTIONS (schema 'HR', table 'EMPLOYEES');

-- ✅ Correct mapping
CREATE FOREIGN TABLE employees_ext (
    emp_id    INTEGER,
    hire_date TIMESTAMP,     -- matches Oracle DATE semantics
    is_active SMALLINT       -- safer than BOOLEAN for MySQL TINYINT(1)
)
SERVER oracle_server
OPTIONS (schema 'HR', table 'EMPLOYEES');

-- Cast to desired types in a view
CREATE VIEW employees_local AS
SELECT
    emp_id,
    hire_date::DATE   AS hire_date,
    is_active::BOOLEAN AS is_active
FROM employees_ext;

3. FDW Extension Version Incompatibility After PostgreSQL Upgrade

After a major PostgreSQL upgrade (e.g., v14 → v16), FDW extensions must also be updated. An outdated FDW may lack handlers for newer built-in types such as jsonb, uuid, or pg_lsn, causing HV004 at runtime.

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

-- Upgrade FDW extensions
ALTER EXTENSION postgres_fdw UPDATE;
ALTER EXTENSION oracle_fdw  UPDATE;

-- Workaround: receive JSONB as TEXT, cast locally
CREATE FOREIGN TABLE events_ext (
    id         BIGINT,
    payload    TEXT,          -- TEXT instead of JSONB for older FDW
    created_at TIMESTAMPTZ
)
SERVER remote_pg_server
OPTIONS (schema_name 'public', table_name 'events');

CREATE VIEW events_local AS
SELECT id, payload::JSONB AS payload, created_at
FROM events_ext;

Quick Fix Checklist

Drop and recreate the foreign table using only base PostgreSQL types (TEXT, INTEGER, NUMERIC, TIMESTAMP).
Use views to cast base types into the desired target types on the local side.
Run ALTER EXTENSION … UPDATE for all FDW extensions after any major PostgreSQL upgrade.
Re-import the remote schema with IMPORT FOREIGN SCHEMA to keep definitions in sync automatically.

-- Sync foreign table definitions automatically
IMPORT FOREIGN SCHEMA public
    LIMIT TO (orders, customers)
    FROM SERVER remote_pg_server
    INTO fdw_staging;

Prevention Tips

Validate type compatibility before deployment. Audit all foreign table column types for USER-DEFINED or ARRAY types before applying DDL changes to production.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name IN (
    SELECT foreign_table_name FROM information_schema.foreign_tables
)
  AND data_type IN ('USER-DEFINED', 'ARRAY', 'jsonb');

Keep FDW extensions up to date. Establish a policy to upgrade FDW extensions immediately after any PostgreSQL minor or major version update, and document the supported type matrix for each FDW driver your team relies on.

Related Error Codes

Code	Name	Notes
HV000	fdw_error	Generic FDW error, often logged alongside HV004
HV005	fdw_invalid_data_type_descriptors	Bad type attributes (precision/scale), not the type itself
HV021	fdw_inconsistent_descriptor_information	Column count or order mismatch with remote table
42804	datatype_mismatch	Engine-level type mismatch, can accompany HV004

📖 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