oracle_fdw 的简介
- PostgreSQL 的 oracle_fdw 扩展是一个外部数据包装器，允许您通过外部表访问 Oracle 表和视图（包括物化视图）。
- https://github.com/laurenz/oracle_fdw
- 当 PostgreSQL 客户端访问外部表时，oracle_fdw 通过 PostgreSQL 服务器上的 Oracle 调用接口 (OCI) 库访问外部 Oracle 数据库中的相应数据。
oracle_fdw 使用限制
- 理论上可以在 PostgreSQL 和 Oracle 客户端支持的任何平台上编译和运行。
- 需要 PostgreSQL 9.3 或更高版本，但是不支持以下 PostgreSQL 版本：9.6.0 到 9.6.8 和 10.0 到 10.3。
- 需要 Oracle 客户端版本 11.2 或更高版本。
- 支持的 Oracle 服务器版本取决于使用的客户端版本。
- IMPORT FOREIGN SCHEMA 不适用于 Oracle 服务器 8i。
需要先安装 oracle 客户端
- oracle客户端下载地址
https://www.oracle.com/cn/database/technologies/instant-client/linux-x86-64-downloads.html
rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm rpm -ivh oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
- 添加 oracle 的环境变量
-- TNS_ADMIN and ORACLE_HOME
export TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
-- PATH and LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
- 配置 oracle 动态库
echo "/usr/lib/oracle/11.2/client64/lib" >> /etc/ld.so.conf
ldconfig
- 测试连接oracle
[root@pgtest2 ~]# sqlplus system/oracle@//192.168.0.51:1521/orcl
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 10 10:47:33 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
安装 oracle_fdw
- 确保 pg_config 在环境变量 PATH 中
[root@pgtest2 ~]# which pg_config /enmo/app/pgsql/13.3/bin/pg_config
- 解压安装包，编译安装
-- 解压
[root@pgtest2 soft]# unzip oracle_fdw-master.zip
-- 编译安装
[root@pgtest2 soft]# cd oracle_fdw-master/
[root@pgtest2 oracle_fdw-master]# make
[root@pgtest2 oracle_fdw-master]# make install
-- 使用超级用户安装插件
[root@pgtest2 ~]# psql -U postgres
postgres=# CREATE EXTENSION oracle_fdw with schema public;
CREATE EXTENSION
- 编译安装一个报错
postgres=# CREATE EXTENSION oracle_fdw with schema public;
ERROR: could not load library "/enmo/app/pg13/13.3/lib/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory
不知道为啥 LD_LIBRARY_PATH 环境变量不起作用，还是在 /etc/ld.so.conf 中加上 Oracle 的动态库吧
echo "/usr/lib/oracle/11.2/client64/lib" >> /etc/ld.so.conf
ldconfig
- 检查插件的安装
需要注意，psql 命令 \dx 显示的扩展插件版本不是 oracle_fdw 的安装版本，要获取 oracle_fdw 版本，请使用函数 oracle_diag.
postgres=# \dx oracle_fdw
List of installed extensions
Name | Version | Schema | Description
------------+---------+--------+----------------------------------------
oracle_fdw | 1.2 | public | foreign data wrapper for Oracle access
(1 row)
postgres=# select oracle_diag();
oracle_diag
-----------------------------------------------------------------------------------------------------------------
oracle_fdw 2.5.0devel, PostgreSQL 13.3, Oracle client 11.2.0.4.0, ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib
(1 row)
使用 oracle_fdw
-
要使 oracle_fdw 访问 Oracle 数据库，需要指定以下设置。
-
创建外部服务器 SERVER ，配置 Oracle 的连接
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.0.51:1521/orcl');
-- oradb： 自定义一个 SERVER NAME
-- OPTIONS: 有3个参数--dbserver、isolation_level、nchar
-- dbserver(必需)：定义连接 Oracle 数据库的连接字符串。
-- isolation_level(可选，默认为 serializable): 在 Oracle 数据库中使用的事务隔离级别，可设置的参数值 serializable、read_committed、read_only。
-- nchar(可选，默认为 off): 是否开启 Oracle 端的字符转换，这个参数的开启对性能有很大影响。
-- 这个 SERVER 可以赋权给普通用户使用
GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
-- 查看
postgres=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
-------+----------+----------------------+-------------------+------+---------+---------------------------------------+-------------
oradb | postgres | oracle_fdw | | | | (dbserver '//192.168.0.51:1521/orcl') |
(1 row)
- 可以使用普通用户(超级用户也没问题)创建 PostgreSQL 和 Oracle 之间的用户映射
CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'system', password 'oracle');
-- postgres： PostgreSQL 中已存在的用户
-- oradb： 已创建的 SERVER NAME
-- OPTIONS： 有2个参数--user、password
-- user(必需)：Oracle 用户名
-- password(必需)：Oracle 用户的密码
-- 查看，密码会存储在数据库中
postgres=# \deu+
List of user mappings
Server | User name | FDW options
--------+-----------+--------------------------------------
oradb | postgres | ("user" 'system', password 'oracle')
(1 row)
oracle_fdw 的使用示例
- Oracle 端的表的信息
SQL> @desc emp_list
Name Null? Type
------------------------------- -------- ----------------------------
1 EMPNO NOT NULL NUMBER(4)
2 ENAME VARCHAR2(10)
3 JOB VARCHAR2(9)
4 MGR NUMBER(4)
5 HIREDATE DATE
6 SAL NUMBER(7,2)
7 COMM NUMBER(7,2)
8 DEPTNO NUMBER(2)
-- emp_list 表里面有一条数据
SQL> select EMPNO,ENAME,DEPTNO from emp_list;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1111 aaaa 10
PostgreSQL 端创建外部表
想要在 PostgreSQL 中操作 Oracle 的 emp_list 表，首先在 PostgreSQL 中创建外部表，外部表指向 Oracle 的 emp_list 表，外部表有两种创建方式，任选其一
- PostgreSQL 外部表创建方式一，CREATE FOREIGN TABLE
需要注意：- 外部表的字段名不需要与 Oracle 的表保持一致，但是字段顺序需要与 Oracle 的表保持一致
- 外部表的主键需要与 Oracle 的表保持一致
- OPTIONS 属性里的 schema 和 table 名称必须大写，不然 PostgreSQL 端操作外部表会报 ‘ORA-00942: table or view does not exist’。
- OPTIONS 属性里的 schema 和 table 必须用单引号
- 必须定义 oracle_fdw 可以转换的列
如果数据的长度超过了实际的列长度，就会出现运行时错误。另外请注意，数据类型的行为可能不同，例如浮点数据类型和日期时间数据类型中的分数舍入。
请记住，默认情况下，Oracle 中 CHAR 和 VARCHAR2 类型的长度以字节为单位指定，而 PostgreSQL 的 CHAR、VARCHAR 和 TEXT 类型的长度以字符为单位指定。
drop FOREIGN TABLE ora_emp_list;
CREATE FOREIGN TABLE ora_emp_list
( empno NUMERIC(4,0) OPTIONS (key 'true') NOT NULL
, ename VARCHAR(10)
, job VARCHAR(9)
, mgr NUMERIC(4,0)
, hiredate TIMESTAMP
, sal NUMERIC(7,2)
, comm NUMERIC(7,2)
, deptno NUMERIC(2,0)
)
SERVER oradb OPTIONS (schema 'SYSTEM', table 'EMP_LIST');
-- ora_emp_list： PostgreSQL 自定义外部表名
-- oradb： 已创建的 SERVER NAME
-- 外部表 OPTIONS: 有7个参数--table、dblink、schema、max_long、readonly、sample_percent、prefetch
-- table(必需)：Oracle 端的表名，必须大写，也可以自定义查询，后面有示例
-- dblink(可选): 访问表所需的 Oracle 端的 database link，必须大写
-- schema(可选): Oracle 端的表所属用户，适用于访问不属于连接 Oracle 用户的表，必须大写
-- max_long(可选，默认是 '32767'): Oracle 表中 LONG、LONG RAW 和 XMLTYPE 列的最大长度。如果 max_long 小于检索到的最长值的长度，您将收到错误消息 ORA-01406: fetched column value was truncated。
-- readonly(可选，默认是 'false'): 仅在此选项未设置为 yes/on/true 的表上才允许 DML 操作。
-- sample_percent(可选，默认是 '100'): 此选项仅影响 ANALYZE 处理，可用于在合理的时间内 ANALYZE 非常大的表。
-- prefetch(可选，默认是 '200'): 设置在外部表扫描期间通过 PostgreSQL 和 Oracle 之间的单次往返获取的行数。该值必须介于 0 和 10240 之间，其中零值禁用预读。
-- 列 OPTIONS: 有2个参数--key、strip_zeros
-- key(可选，默认是 'false'): 如果设置为 yes/on/true，则外部 Oracle 表上的相应列被视为主键列。要使 UPDATE 和 DELETE 起作用，您必须在属于表主键的所有列上设置此选项。
-- strip_zeros(可选，默认是 'false'): 如果设置为 yes/on/true，ASCII 0 字符将在传输过程中从字符串中删除。此类字符在 Oracle 中有效，但在 PostgreSQL 中无效，因此在被 oracle_fdw 读取时会导致错误。character此选项仅对,character varying 和 text 列有意义。
- PostgreSQL 外部表创建方式二，IMPORT FOREIGN SCHEMA
需要注意：- 这种方式不需要指定表结构，但是外部表名需要一致，也就是当前 PostgreSQL 的 SCHEMA 下不能存在同名表，否则创建失败。
- 从 PostgreSQL 9.5 开始，支持 IMPORT FOREIGN SCHEMA 为 Oracle 模式中的所有表批量导入表定义。
- IMPORT FOREIGN SCHEMA 将为在 ALL_TAB_COLUMNS 中找到的所有对象创建外部表。这包括表、视图和物化视图，但不包括同义词。
- Oracle SCHEMA 名称通常为大写。由于 PostgreSQL 在处理之前将名称转换为小写，因此您必须用双引号保护 SCHEMA 名称（例如"SCOTT"）。
- LIMIT TO 导入括号内包含的表，多个表以逗号分隔，EXCEPT 导入不包含(排除)括号内的表，多个表以逗号分隔
-- 导入单表
IMPORT FOREIGN SCHEMA "SCOTT" limit to (EMP) from server oradb into public;
-- 导入多表
IMPORT FOREIGN SCHEMA "SCOTT" LIMIT TO (EMP,DEPT) from server oradb into public OPTIONS (readonly 'true', prefetch '100');
-- 排除表导入
IMPORT FOREIGN SCHEMA "SCOTT" EXCEPT (EMP,DEPT) from server oradb into public;
-- IMPORT FOREIGN SCHEMA 支持的选项：
-- case(默认：smart)：控制导入期间表名和列名的大小写，参数值：
-- keep: 保留 Oracle 中的名称，通常为大写。
-- lower: 将所有表名和列名转换为小写。
-- smart: 仅转换 Oracle 中全部大写的名称。
-- collation(默认：default)：用于 case 选项的 lower 和 smart 选项的排序规则
-- dblink
-- readonly
-- max_long
-- sample_percent
-- prefetch
- 查看已创建的外部表信息
postgres=# \det+
List of foreign tables
Schema | Table | Server | FDW options | Description
--------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------+-------------
public | bonus | oradb | (schema 'SCOTT', "table" 'BONUS') |
public | dept | oradb | (schema 'SCOTT', "table" 'DEPT', readonly 'true', prefetch '100') |
public | emp | oradb | (schema 'SCOTT', "table" 'EMP', readonly 'true', prefetch '100') |
public | ora_emp_list | oradb | (schema 'SYSTEM', "table" 'EMP_LIST') |
public | ora_emp_list_dept | oradb | ("table" '(select a.EMPNO, a.ENAME, a.DEPTNO, b.DNAME from EMP_LIST a, dept b where a.DEPTNO = b.DEPTNO and a.JOB = ''CLERK'')') |
public | salgrade | oradb | (schema 'SCOTT', "table" 'SALGRADE') |
(6 rows)
- PostgreSQL 外部表是否可以只关联 Oracle 表的某几个字段呢？比如示例中的 emp_list 表有8个字段，我只想关联3个字段（empno，ename，deptno）。
drop FOREIGN TABLE ora_emp_list;
CREATE FOREIGN TABLE ora_emp_list
( empno NUMERIC(4,0) OPTIONS (key 'true') NOT NULL
, ename VARCHAR(10)
, deptno NUMERIC(2,0)
)
SERVER oradb OPTIONS (table '(SELECT empno,ename,deptno FROM EMP_LIST)');
-- 在这种情况下不要设置 schema 选项。
postgres=# select * from ora_emp_list;
empno | ename | deptno
-------+--------+--------
7782 | CLARK | 10
7839 | KING | 10
7934 | MILLER | 10
... ...
- PostgreSQL 外部表的创建也可以实现自定义查询
-- Oracle
SQL> select * from EMP_LIST;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select a.EMPNO, a.ENAME, a.DEPTNO, b.DNAME from EMP_LIST a, dept b where a.DEPTNO = b.DEPTNO and a.JOB = 'CLERK';
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7934 MILLER 10 ACCOUNTING
7369 SMITH 20 RESEARCH
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
-- 以下实现自定义查询创建 PostgreSQL 外部表
drop FOREIGN TABLE ora_emp_list_dept;
CREATE FOREIGN TABLE ora_emp_list_dept
( empno NUMERIC(4,0) OPTIONS (key 'true') NOT NULL
, ename VARCHAR(10)
, deptno NUMERIC(2,0)
, dname VARCHAR(10)
)
SERVER oradb OPTIONS (table '(select a.EMPNO, a.ENAME, a.DEPTNO, b.DNAME from EMP_LIST a, dept b where a.DEPTNO = b.DEPTNO and a.JOB = ''CLERK'')');
-- 在这种情况下不要设置 schema 选项。
postgres=# select * from ora_emp_list_dept;
empno | ename | deptno | dname
-------+--------+--------+------------
7934 | MILLER | 10 | ACCOUNTING
7369 | SMITH | 20 | RESEARCH
7876 | ADAMS | 20 | RESEARCH
7900 | JAMES | 30 | SALES
(4 rows)
修改外部表属性
-- 关闭外部表的只读属性
postgres=# alter foreign table emp OPTIONS (SET readonly 'no');
访问 Oracle 数据库
- 客户端发送对外部表的查询
- PostgreSQL 请求 oracle_fdw 获取 Oracle 表的执行计划和表数据
- oracle_fdw 读取外部服务器和用户映射信息并返回访问信息
- oracle_fdw 通过 OCI 库将查询发送到 Oracle 数据库
- oracle_fdw 获取结果返回给 PostgreSQL
- 客户端收到结果
postgres=# select * from dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)
-- 查看执行计划，执行计划显示 正在访问 Oracle 端的表
postgres=# EXPLAIN ANALYZE VERBOSE select * from dept;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.dept (cost=10000.00..20000.00 rows=1000 width=92) (actual time=0.351..0.362 rows=4 loops=1)
Output: deptno, dname, loc
Oracle query: SELECT /*62b5e8b0d88d7cfb*/ r1."DEPTNO", r1."DNAME", r1."LOC" FROM "SCOTT"."DEPT" r1
Oracle plan: SELECT STATEMENT
Oracle plan: TABLE ACCESS FULL DEPT
Planning Time: 0.679 ms
Execution Time: 0.397 ms
(7 rows)
-- 以 Foreign Scan 开头的行显示在 Oracle 端访问的表。
-- 以 Oracle query 开头的行显示了在 Oracle 端执行的 SQL 语句（因为我们指定了ANALYZE）
-- 以 Oracle plan 开头的行显示了 Oracle 端的执行计划（因为我们指定了 VERBOSE）
DML 操作
- PostgreSQL 在外部表做 DML 操作，也会直接作用到 Oracle 上的表
-- insert
postgres=# insert into ora_emp_list (empno,ename,deptno) values (2222,'bbbb',20);
SQL> select EMPNO,ENAME,DEPTNO from emp_list;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1111 aaaa 10
2222 bbbb 20 --<<<<<<<
-- update
postgres=# update ora_emp_list set ename='cccc' where empno = 2222;
SQL> select EMPNO,ENAME,DEPTNO from emp_list;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1111 aaaa 10
2222 cccc 20 --<<<<<<<
-- delete
postgres=# delete from ora_emp_list where deptno=20;
SQL> select EMPNO,ENAME,DEPTNO from emp_list;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1111 aaaa 10
- update 和 delete 都要求 Oracle 上的表某一列存在主键，并配置 列OPTIONS 的 key
postgres=# update ora_emp_list set empno = 2222 where ename='aaaa';
ERROR: UPDATE on Oracle table changed 2 rows instead of one in iteration 0
HINT: This probably means that you did not set the "key" option on all primary key columns.
postgres=# delete from ora_emp_list where ename='aaaa';
ERROR: DELETE on Oracle table removed 2 rows instead of one in iteration 0
HINT: This probably means that you did not set the "key" option on all primary key columns.
- 修改 Oracle 表的字段名，对 PostgreSQL 的外部表无影响
SQL> alter table emp_list rename column ENAME to ENAME_OLD;
postgres=# update ora_emp_list set empno = 2222 where ename='cccc';
postgres=# update ora_emp_list set ename='cccc' where empno = 2222;
SQL> select EMPNO,ENAME_OLD,DEPTNO from emp_list;
EMPNO ENAME_OLD DEPTNO
---------- ---------- ----------
1111 aaaa 10
2222 cccc 20
oracle_fdw 的函数
-- oracle_fdw 创建的函数
postgres=# \df oracle_*
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------------+------------------+-----------------------------+------
public | oracle_close_connections | void | | func
public | oracle_diag | text | name DEFAULT NULL::name | func
public | oracle_execute | void | server name, statement text | func
public | oracle_fdw_handler | fdw_handler | | func
public | oracle_fdw_validator | void | text[], oid | func
(5 rows)
-- oracle_fdw_handler 和 oracle_fdw_validator 是创建外部数据包装器所必需的处理程序和验证器函数。
FOREIGN DATA WRAPPER oracle_fdw
HANDLER oracle_fdw_handler
VALIDATOR oracle_fdw_validator
函数 oracle_close_connections
oracle_fdw 会缓存 Oracle 连接，因为为每个单独的查询创建 Oracle 会话的成本很高。当 PostgreSQL 会话结束时，所有连接都会自动关闭。
函数 oracle_close_connections() 可用于关闭所有缓存的 Oracle 连接，但是不能在修改 Oracle 数据的事务中调用此函数。
-- 在 Oracle 数据库中可以看到有一个 INACTIVE 的会话连接
SQL> select SADDR,SID,USERNAME,STATUS,OSUSER,MACHINE,TYPE,SQL_ID,LOGON_TIME,PREV_SQL_ID from v$session where type='USER';
SADDR SID USERNAME STATUS OSUSER MACHINE TYPE SQL_ID LOGON_TIME PREV_SQL_ID
---------------- ---------- ---------- -------- ---------- ---------- ---------- --------------- ------------------- -------------
000000011C66AC50 8 SYSTEM INACTIVE postgres pgtest2 USER 2022-04-11 04:24:22 bt3snu1v4nrwy
-- 此会话上次执行的 SQL_ID 是 bt3snu1v4nrwy，可以看到这个 SQL
SQL> @xi bt3snu1v4nrwy %
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID bt3snu1v4nrwy, child number 0
-------------------------------------
SELECT /*b0ef4f195f3c027d*/ r1."EMPNO", r1."ENAME", r1."DEPTNO",
r1."DNAME" FROM (select a.EMPNO, a.ENAME, a.DEPTNO, b.DNAME from
EMP_LIST a, dept b where a.DEPTNO = b.DEPTNO and a.JOB = 'CLERK') r1
Plan hash value: 1507009334
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Pstart| Pstop |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | NESTED LOOPS | | 1 | | |
| 2 | TABLE ACCESS FULL | DEPT | 4 | | |
| 3 | PARTITION LIST ITERATOR| | 1 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | EMP_LIST | 1 | KEY | KEY |
----------------------------------------------------------------------
-- 在 PostgreSQL 端使用函数 oracle_close_connections() 清理 oracle_fdw 缓存的 Oracle 连接。
postgres=# select oracle_close_connections();
oracle_close_connections
--------------------------
(1 row)
-- 在 Oracle 数据库中再次查询会话连接情况，就看不到 oracle_fdw 的连接会话。
SQL> select SADDR,SID,USERNAME,STATUS,OSUSER,MACHINE,TYPE,SQL_ID,LOGON_TIME,PREV_SQL_ID from v$session where type='USER';
函数 oracle_diag
- 此函数仅用于诊断目的。
- 它将返回 oracle_fdw、PostgreSQL 服务器和 Oracle 客户端的版本。如果调用时不带参数或者指定 NULL ，它将额外返回一些用于建立 Oracle 连接的环境变量的值。
- 如果调用时指定外部服务器的名称，它将额外返回 Oracle 服务器版本。
postgres=# select oracle_diag();
oracle_diag
-----------------------------------------------------------------------------------------------------------------
oracle_fdw 2.5.0devel, PostgreSQL 13.3, Oracle client 11.2.0.4.0, ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib
(1 row)
postgres=# select oracle_diag('oradb');
oracle_diag
--------------------------------------------------------------------------------------------
oracle_fdw 2.5.0devel, PostgreSQL 13.3, Oracle client 11.2.0.4.0, Oracle server 11.2.0.4.0
(1 row)
函数 oracle_execute
- 该函数可用于在远程 Oracle 服务器上执行任意 SQL 语句。这仅适用于不返回结果的语句（通常是 DDL 语句）。
- 使用该函数时要小心，因为它可能会干扰 oracle_fdw 的事务管理。请记住，在 Oracle 中运行 DDL 语句将发出隐式 COMMIT。最好建议您在多版本并发事务之外使用此功能。
-- 注意 SQL 语句结尾不要加分号
postgres=# SELECT oracle_execute('oradb','drop table system.t111');
oracle_execute
----------------
(1 row)
postgres=# SELECT oracle_execute('oradb','drop table system.t111');
ERROR: error executing statement: OCIStmtExecute failed to execute query
DETAIL: ORA-00942: table or view does not exist
下推
外部数据包装器(Foreign data wrappers)采用一种称为 pushdown 的机制，它允许远程端执行 WHERE、ORDER BY 和 JOIN 子句。下推 WHERE 和 JOIN 减少了本地和远程服务器之间传输的数据量，避免了网络通信瓶颈。
WHERE 下推
如果 SQL 语句有 WHERE 子句，则将查询条件传递给 Oracle 数据库执行，包括其中调用的任何函数。
postgres=# EXPLAIN ANALYZE VERBOSE select ename,sal from emp where sal <= 2000;
ORDER BY 下推
一般情况下，如果SQL语句有ORDER BY子句不包含字符类型列，就会被下推，数据会在Oracle端进行排序。
- 如果该子句包含字符类型的列，则不会下推，因为 oracle_fdw 不能保证 Oracle 和 PostgreSQL 的排序顺序相同。
- 如果可以保证两边的排序顺序相同，则该子句将被下推，数字和日期时间数据类型就是这种情况，对于其他数据类型，需要单独验证操作。
- 如果语句还包含 JOIN，则不会下推该子句。
排序字段是数值类型–下推
排序字段是时间类型–下推
排序字段是字符类型–不下推
几个函数的测试
postgres=# EXPLAIN ANALYZE select ename,sal from emp limit 5;
postgres=# EXPLAIN ANALYZE VERBOSE SELECT SUBSTR(job,2,3) a FROM emp where empno = 7839;
postgres=# EXPLAIN ANALYZE VERBOSE select ename,sal from emp where sal >= POWER(2,6);
postgres=# EXPLAIN ANALYZE VERBOSE select wm_concat(ename) from emp where deptno=10;
JOIN 是否被下推
请注意以下对 JOIN 子句下推的限制：
- JOIN 子句子句必须指定在 SELECT 语句中
- 要连接的表必须在同一个外部服务器上定义
- 它必须只涉及 2 个表 - 附加表的连接将在 PostgreSQL 端执行
- 如果 SELECT 语句包含 JOIN 和 WHERE，则两者都被下推
- 如果 SELECT 语句包含 JOIN 和 ORDER BY，那么只有 JOIN 被下推
- 没有连接条件的交叉连接不会下推该子句
JOIN 三个外部表
postgres=# EXPLAIN ANALYZE select t1.ename from emp t1
INNER JOIN dept t2 ON t1.deptno = t2.deptno
INNER JOIN bonus t3 ON t1.ename = t3.ename;
- 在 PostgreSQL 端执行表 t1 和 t3 的连接
- 表 t1 和 t2 的联接被下推到 Oracle
JOIN 和 ORDER BY
postgres=# EXPLAIN ANALYZE select t1.ename,t1.sal from emp t1
INNER JOIN dept t2 ON t1.deptno = t2.deptno
order by t1.sal;
- JOIN 被下推但 ORDER BY 没有被下推
更新事务和序列化错误
oracle_fdw 还支持更新事务，使用 SERIALIZABLE 事务隔离级别来确保一致性。这是因为单个 SQL 语句可能会向 Oracle 数据库生成多个 SQL 语句。因此，使用多个并发事务更新外部表可能会导致序列化错误。
为避免这种情况，请确保应用程序不会同时更新外部表。如果发生序列化错误，则回滚事务并再次执行。
oracle_fdw 不支持预准备语句（PREPARE）和两阶段提交（PREPARE TRANSACTION 等），因为它们需要控制 Oracle 数据库。
-- 第一个会话执行 update 不提交，当前会话可以看到更改后的数据
postgres=# begin;
BEGIN
postgres=*# update emp set sal=1000 where empno=7369;
UPDATE 1
postgres=*# select * from emp where empno=7369;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-------+------+---------------------+---------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 1000.00 | | 20
(1 row)
-- 第二个会话和在Oracle 上均查询不到 第一个会话 未提交的数据
postgres=# select * from emp where empno=7369;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-------+------+---------------------+--------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20
(1 row)
SQL> select * from scott.emp where empno=7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
-- 在第二个会话上执行 update 更新同一条记录，被堵塞
postgres=# begin;
BEGIN
postgres=*# update emp set sal=2000 where empno=7369;
-- 在 Oracle 也执行 update 更新同一条记录，也被堵塞
SQL> update scott.emp set sal=2000 where empno=7369;
-- 第一个会话 提交事务
postgres=*# commit;
COMMIT
-- 第二个会话报错ORA-08177，事务终止并回滚，只能重启事务
postgres=*# update emp set sal=2000 where empno=7369;
ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-08177: can't serialize access for this transaction
postgres=!# update emp set sal=2000 where empno=7369;
ERROR: current transaction is aborted, commands ignored until end of transaction block
postgres=!# commit;
ROLLBACK
外部表定义约束和默认值
创建外部表时，建议匹配远程表(Oracle上的表)中定义的约束（CHECK、NOT NULL 等）和默认值（DEFAULT）。
虽然 oracle_fdw 将默认值应用于使用 DEFAULT 创建的列，但它不检查约束，约束是在 Oracle 端检查。
oracle_fdw 不检查约束
在下面的示例中，我们尝试为 emp_id 列（在 Oracle 中使用 NOT NULL 创建）和 dept 列（在 PostgreSQL 和 Oracle 中使用 DEFAULT 10 创建）插入一个具有 NULL 值的行。
-- oracle
create table system.tbl3 (
ID number(6) primary key,
EMP_ID number(6) NOT NULL,
NAME varchar2(32),
DEPT number(6) DEFAULT 10);
insert into system.tbl3 values (123,321,'aaaa',DEFAULT);
SQL> select * from system.tbl3;
ID EMP_ID NAME DEPT
---------- ---------- ------------ ----------
123 321 aaaa 10
-- PostgreSQL
CREATE FOREIGN TABLE f_ora_tbl3 (
id integer OPTIONS (key 'true'),
emp_id integer NOT NULL,
name varchar(32),
dept integer DEFAULT 10)
SERVER oradb OPTIONS (schema 'SYSTEM', table 'TBL3');
postgres=# select * from f_ora_tbl3;
id | emp_id | name | dept
-----+--------+------+------
123 | 321 | aaaa | 10
(1 row)
在发送语句之前，oracle_fdw 将 dept 列的 ‘default’ 替换为 ‘10’，但它不验证 emp-id 列的 NULL 约束，并按原样发送值。这将导致 Oracle 中的约束冲突。
postgres=# INSERT INTO f_ora_tbl3 VALUES (10, NULL, 'abc', default);
ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-01400: cannot insert NULL into ("SYSTEM"."TBL3"."EMP_ID")
如果主键指定错误
即使你没有在外表上定义约束来匹配 Oracle 表，只要 Oracle 端没有违反约束，SQL 语句也不会返回错误。
在下面的示例中，我们创建了一个表并错误地将 emp_id 指定为主键，而不是 id。
之后，我们执行 2 次 INSERT，将相同的值 ‘9’ 添加到不正确的主键中，这不会产生错误，因为就远程 Oracle 表而言，对 emp_id 的唯一约束是 NOT NULL。
但是，稍后当我们尝试执行 UPDATE 和 DELETE 时，我们收到一个错误，因为现在我们在外表上的主键约束之间存在冲突。
-- PostgreSQL 创建外部表，错误的指定了主键列
drop FOREIGN TABLE f_ora_tbl3;
CREATE FOREIGN TABLE f_ora_tbl3 (
id integer NOT NULL,
emp_id integer OPTIONS (key 'true'),
name varchar(32),
dept integer DEFAULT 10)
SERVER oradb OPTIONS (schema 'SYSTEM', table 'TBL3');
-- id 列使用 NOT NULL 而不是 OPTIONS(key 'true') 指定主键列
-- emp_id 列使用 OPTIONS(key 'true') 而不是指定 NOT NULL
postgres=# select * from f_ora_tbl3;
id | emp_id | name | dept
-----+--------+------+------
123 | 321 | aaaa | 10
(1 row)
-- 插入数据
postgres=# INSERT INTO f_ora_tbl3 VALUES(1, 9,'aaa',7);
INSERT 0 1
postgres=# INSERT INTO f_ora_tbl3 VALUES(2, 9,'bbb',4);
INSERT 0 1
-- 没有检查外部表的主键约束，所以插入了行
postgres=# select * from f_ora_tbl3;
id | emp_id | name | dept
-----+--------+------+------
123 | 321 | aaaa | 10
1 | 9 | aaa | 7
2 | 9 | bbb | 4
(3 rows)
-- 现在对于外部表是存在主键冲突的
-- UPDATE 和 DELETE 失败并出现 oracle_fdw 错误
postgres=# UPDATE f_ora_tbl3 SET name='ccc' WHERE id=2;
ERROR: UPDATE on Oracle table changed 2 rows instead of one in iteration 0
HINT: This probably means that you did not set the "key" option on all primary key columns.
postgres=# DELETE FROM f_ora_tbl3 WHERE id=2;
ERROR: DELETE on Oracle table removed 2 rows instead of one in iteration 0
HINT: This probably means that you did not set the "key" option on all primary key columns.
如果未指定默认值
在下面的示例中，我们创建了一个表并错误地指定了没有默认值 10 的 dept 列。然后，我们为 dept 列插入了一个指定“默认”的行 - 因为外部表没有指定默认值，oracle-fdw 会将其替换为 NULL 并将其发送给 Oracle。由于在 Oracle 端未使用 NOT NULL 指定该列，因此该语句将成功创建在 dept 列上具有 NULL 的行，这不是预期的结果。
-- PostgreSQL 创建外部表，dept 列未指定默认值
drop FOREIGN TABLE f_ora_tbl3;
CREATE FOREIGN TABLE f_ora_tbl3 (
id integer OPTIONS (key 'true'),
emp_id integer NOT NULL,
name varchar(32),
dept integer)
SERVER oradb OPTIONS (schema 'SYSTEM', table 'TBL3');
postgres=# select * from f_ora_tbl3;
id | emp_id | name | dept
-----+--------+------+------
123 | 321 | aaaa | 10
1 | 9 | aaa | 7
2 | 9 | bbb | 4
(3 rows)
postgres=# INSERT INTO f_ora_tbl3 VALUES (10, 100, 'abc', default);
INSERT 0 1
-- dept 列被插入了 NULL 值，这不是预期的结果
postgres=# select * from f_ora_tbl3 where id=10;
id | emp_id | name | dept
----+--------+------+------
10 | 100 | abc |
(1 row)
----------------end
参考文献
https://github.com/laurenz/oracle_fdw
https://www.postgresql.fastware.com/postgresql-insider-fdw-ora-bas
https://www.postgresql.fastware.com/postgresql-insider-fdw-ora-adv
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
