How to fix HV021: fdw_inconsistent_descriptor_information in PostgreSQL

-- 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;

-- 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%';

-- 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;

-- 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');

-- 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;

-- 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;