使用PostgreSQL odbc_fdw连接Oracle/DB2时数据显示异常求助

问题现象 #

通过odbc_fdw连接Oracle与DB2并查询数据时，count(*)返回的记录数正常，但查询全量字段时出现异常：第一个字段值始终为#，从第二个字段起所有值均为null。

环境信息 #

• 服务器操作系统：Windows Server 2019 Standard
• PostgreSQL版本：postgresql-x64-16
• PostgreSQL数据库字符集：UTF8（已排除字符编码问题）
• ODBC配置：系统侧ODBC数据源定义正确，可通过A5:SQL Mk-2正常访问目标库
• odbc_fdw版本：测试过两个0.5.2.3版本，结果一致
  • 预编译Windows二进制版本
  • 源码编译版本
• 其他FDW模块状态：
  • oracle_fdw：正常（可正常访问Oracle，日文数据无问题）
  • db2_fdw：异常（疑似仅支持英文Windows环境）
  • tds_fdw：异常（Windows环境不支持）
  • jdbc_fdw：异常（编译报错，Windows环境可能不支持）

测试结果 #

Oracle测试结果 #

postgres=# select count(*) from odbc_zzzz_test_tbl4;
 count
-------
      2
(1 row)

postgres=# select * from odbc_zzzz_test_tbl4;
 data_id | name1 | name2 | update_d
---------+-------+-------+----------
 #       |       |       |
 #       |       |       |
(2 rows)

DB2测试结果 #

postgres=# select count(*) from odbc_yyyy_CONNHEADER_SCMDWH_EVENT;
 count
-------
  1263
(1 row)

postgres=# select * from odbc_yyyy_CONNHEADER_SCMDWH_EVENT;
 appl_id | appl_name | client_protocol
---------+-----------+-----------------
 #       |           |
 #       |           |
 #       |           |
 #       |           |
 (其余省略)

外部表定义 #

Oracle外部表定义 #

CREATE SERVER odbc_zzzz
FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (dsn 'zzzz', encoding 'SJIS');

CREATE USER MAPPING FOR postgres
SERVER odbc_zzzz
OPTIONS (odbc_UID 'xxxx', odbc_PWD 'xxxx');

CREATE FOREIGN TABLE
  odbc_zzzz_test_tbl4 (
    DATA_ID text,
    NAME1 text,
    NAME2 text,
    UPDATE_D date
  )
  SERVER odbc_zzzz
  OPTIONS (
    sql_query 'select DATA_ID,NAME1,NAME2,UPDATE_D from ssss.test_tbl',
    sql_count 'select count(DATA_ID) from ssss.test_tbl'
  );

Oracle原表定义

CREATE TABLE ssss.TEST_TBL
(
    DATA_ID                        VARCHAR2(8),
    NAME1                          VARCHAR2(10),
    NAME2                          VARCHAR2(50),
    UPDATE_D                       DATE
)

DB2外部表定义 #

CREATE SERVER odbc_yyyy 
FOREIGN DATA WRAPPER odbc_fdw 
OPTIONS (dsn 'yyyy', encoding 'SJIS');

CREATE USER MAPPING FOR postgres
SERVER odbc_yyyy
OPTIONS (odbc_UID 'xxxx', odbc_PWD 'xxxx');

CREATE FOREIGN TABLE
  odbc_yyyy_CONNHEADER_SCMDWH_EVENT (
    APPL_ID text,
    APPL_NAME text,
    CLIENT_PROTOCOL integer
  )
  SERVER odbc_yyyy
  OPTIONS (
    sql_query 'select APPL_ID,APPL_NAME,CLIENT_PROTOCOL from aaaa.bbbb_tbl',
    sql_count 'select count(APPL_ID) from aaaa.bbbb_tbl'
  );

原因分析与解决方案 #

原因分析 #

该异常通常由以下原因导致：

1. 版本兼容性bug：odbc_fdw 0.5.2.3与PostgreSQL 16在Windows平台下存在适配问题，导致结果集读取时缓冲区处理错误，第一个字段被填充无效占位符#，后续字段无法正确解析。
2. 字段类型映射不匹配：外部表定义的字段类型（如text）与ODBC驱动返回的数据类型未正确适配，引发数据读取失败。
3. 自定义查询语句的绑定问题：使用sql_query手动指定查询时，字段顺序或类型的隐式转换导致odbc_fdw无法正确绑定结果集字段。

解决方案 #

1. 升级odbc_fdw版本：更换为适配PostgreSQL 16的最新版odbc_fdw，新版本通常会修复旧版本的平台兼容性问题。
2. 调整字段类型定义：
   • 对于Oracle的VARCHAR2和DB2的字符类型，将外部表的text改为与原表长度匹配的varchar(n)（如Oracle的DATA_ID定义为varchar(8)）；
   • 对于日期/数值类型，确保外部表类型与目标库类型严格匹配（如Oracle的DATE对应PostgreSQL的date或timestamp）。
3. 移除自定义查询选项：放弃使用sql_query和sql_count，改用自动映射方式创建外部表，让odbc_fdw自动处理字段绑定：

   -- Oracle示例
   CREATE FOREIGN TABLE odbc_zzzz_test_tbl4 (
       DATA_ID varchar(8),
       NAME1 varchar(10),
       NAME2 varchar(50),
       UPDATE_D date
   ) SERVER odbc_zzzz OPTIONS (table 'ssss.test_tbl');
   

4. 校验ODBC驱动位数：确保ODBC驱动（Oracle/DB2）的位数与PostgreSQL、odbc_fdw完全一致（均为64位），位数不匹配会导致数据读取异常。
5. 更新ODBC驱动：更换为Oracle/DB2的最新版ODBC驱动，提升与odbc_fdw的兼容性。

内容的提问来源于stack exchange，提问作者toyo