PostgreSQL raises HV021 when a foreign-data wrapper detects inconsistent descriptor information between the local foreign table definition and the remote server's metadata. This typically happens when column definitions, data types, or constraints mismatch after schema changes on either side. Reconciling the foreign table descriptor with the remote catalog resolves the inconsistency.
HV021 is a SQL/MED error code that PostgreSQL uses when a foreign-data wrapper (FDW) encounters descriptor information that doesn't match expectations. Descriptor information includes metadata about columns, data types, constraints, and other structural details that define how data flows between PostgreSQL and the remote server. When PostgreSQL plans a query involving a foreign table, it builds a descriptor that describes the expected shape and properties of the remote data. If the FDW discovers that the actual remote descriptor (obtained by querying the remote catalog) differs from what was cached or expected, it raises HV021 to prevent data corruption or query failures. This error acts as a safety check, ensuring that PostgreSQL doesn't attempt to process data in ways that would lead to type mismatches, constraint violations, or other inconsistencies.
Examine the local foreign table definition to understand what descriptor PostgreSQL expects.
-- Show detailed foreign table information including column mappings:
\d+ schema_name.foreign_table_name
-- Query system catalogs for FDW-specific information:
SELECT attname, atttypid::regtype, attfdwoptions
FROM pg_attribute
WHERE attrelid = 'schema_name.foreign_table_name'::regclass
AND attnum > 0
ORDER BY attnum;This reveals the column names, data types, and FDW options that PostgreSQL has cached for the foreign table.
Connect directly to the remote server (or use the same connection parameters) to see the actual remote descriptor.
-- If using postgres_fdw to connect to another PostgreSQL instance:
SELECT attname, atttypid::regtype
FROM pg_attribute
WHERE attrelid = 'remote_schema.remote_table'::regclass
AND attnum > 0
ORDER BY attnum;
-- For other FDWs, use appropriate remote catalog queries
-- Check for any recent DDL changes on the remote side:
SELECT schemaname, tablename, operation, query
FROM pg_stat_statements
WHERE query LIKE '%ALTER TABLE%remote_table%';Compare this with the local descriptor to identify mismatches.
Force PostgreSQL to rebuild the cached descriptor by refreshing the foreign table metadata.
-- For postgres_fdw, use ALTER FOREIGN TABLE with OPTIONS refresh:
ALTER FOREIGN TABLE schema_name.foreign_table_name
OPTIONS (ADD refresh 'true');
-- Alternatively, drop and recreate the foreign table connection:
ALTER FOREIGN TABLE schema_name.foreign_table_name
OPTIONS (SET host 'new_host', port '5432');
-- Or use a more aggressive approach - recreate the foreign server:
DROP FOREIGN TABLE schema_name.foreign_table_name;
IMPORT FOREIGN SCHEMA remote_schema
LIMIT TO (remote_table)
FROM SERVER foreign_server
INTO schema_name;This forces the FDW to re-fetch descriptor information from the remote server.
Update the local foreign table definition to match the remote schema exactly.
-- Add missing columns:
ALTER FOREIGN TABLE schema_name.foreign_table_name
ADD COLUMN new_column remote_data_type
OPTIONS (fdw_column_name 'remote_column_name');
-- Remove columns that no longer exist remotely:
ALTER FOREIGN TABLE schema_name.foreign_table_name
DROP COLUMN stale_column;
-- Update data type mappings:
ALTER FOREIGN TABLE schema_name.foreign_table_name
ALTER COLUMN existing_column
OPTIONS (SET fdw_type_name 'correct_remote_type');Ensure every column, data type, and constraint matches between local and remote sides.
Clear any cached descriptor information and restart the query.
-- For postgres_fdw, you may need to clear connection cache:
SELECT postgres_fdw_disconnect('foreign_server_name');
SELECT postgres_fdw_get_connections(); -- Verify connections closed
-- Restart the session or reconnect:
\c database_name
-- Alternatively, restart PostgreSQL service if using persistent connections:
-- sudo systemctl restart postgresql
-- Then retry the failing query:
SELECT * FROM schema_name.foreign_table_name LIMIT 1;This ensures fresh descriptor information is fetched from the remote server.
Ensure FDW extensions are compatible between PostgreSQL instances.
-- Check FDW extension versions:
SELECT extname, extversion
FROM pg_extension
WHERE extname LIKE '%fdw%';
-- Compare with remote server if applicable:
-- On remote server:
SELECT version();
-- Ensure matching FDW options and capabilities:
SHOW postgres_fdw.use_remote_estimate;
SHOW postgres_fdw.fetch_size;
-- Consider upgrading FDW extensions if versions differ significantly:
ALTER EXTENSION postgres_fdw UPDATE;Incompatible FDW versions can cause descriptor parsing differences.
HV021 corresponds to SQL/MED error fdw_inconsistent_descriptor_information, which indicates a metadata mismatch between what the FDW expects and what the remote server provides. PostgreSQL FDWs cache descriptor information to avoid repeated catalog queries, but this cache can become stale when remote schemas change.
Some FDWs (like postgres_fdw) support descriptor refresh mechanisms, while others may require manual reconciliation. The error typically occurs during query planning, not execution, as PostgreSQL validates descriptor consistency before building query plans.
In distributed systems with frequent schema changes, consider implementing schema synchronization tools or using FDWs that support automatic descriptor refresh. For critical production systems, test foreign table queries after any remote DDL operations.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL