暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片

PostgreSQL插件--oracle_fdw

原创 张玉龙 2022-04-11
2557

oracle_fdw 的简介

  • PostgreSQL 的 oracle_fdw 扩展是一个外部数据包装器,允许您通过外部表访问 Oracle 表和视图(包括物化视图)。
  • https://github.com/laurenz/oracle_fdw
  • 当 PostgreSQL 客户端访问外部表时,oracle_fdw 通过 PostgreSQL 服务器上的 Oracle 调用接口 (OCI) 库访问外部 Oracle 数据库中的相应数据。
    image.png

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 数据库,需要指定以下设置。
    image.png

  • 创建外部服务器 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 可以转换的列
      image.png
      如果数据的长度超过了实际的列长度,就会出现运行时错误。另外请注意,数据类型的行为可能不同,例如浮点数据类型和日期时间数据类型中的分数舍入。
      请记住,默认情况下,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 中全部大写的名称。 -- collat​​ion(默认: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 数据库

image.png

  • 客户端发送对外部表的查询
  • 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;

image.png
image.png

ORDER BY 下推

一般情况下,如果SQL语句有ORDER BY子句不包含字符类型列,就会被下推,数据会在Oracle端进行排序。

  • 如果该子句包含字符类型的列,则不会下推,因为 oracle_fdw 不能保证 Oracle 和 PostgreSQL 的排序顺序相同。
  • 如果可以保证两边的排序顺序相同,则该子句将被下推,数字和日期时间数据类型就是这种情况,对于其他数据类型,需要单独验证操作。
  • 如果语句还包含 JOIN,则不会下推该子句。

排序字段是数值类型–下推

image.png
image.png

排序字段是时间类型–下推

image.png
image.png

排序字段是字符类型–不下推

image.png
image.png

几个函数的测试

postgres=# EXPLAIN ANALYZE select ename,sal from emp limit 5;

image.png

postgres=# EXPLAIN ANALYZE VERBOSE SELECT SUBSTR(job,2,3) a FROM emp where empno = 7839;

image.png

postgres=# EXPLAIN ANALYZE VERBOSE select ename,sal from emp where sal >= POWER(2,6);

image.png

postgres=# EXPLAIN ANALYZE VERBOSE select wm_concat(ename) from emp where deptno=10;

image.png

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;

image.png

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

image.png

  • JOIN 被下推但 ORDER BY 没有被下推

更新事务和序列化错误

oracle_fdw 还支持更新事务,使用 SERIALIZABLE 事务隔离级别来确保一致性。这是因为单个 SQL 语句可能会向 Oracle 数据库生成多个 SQL 语句。因此,使用多个并发事务更新外部表可能会导致序列化错误。

为避免这种情况,请确保应用程序不会同时更新外部表。如果发生序列化错误,则回滚事务并再次执行。
image.png
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 值的行。
image.png

-- 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 的行,这不是预期的结果。

image.png

-- 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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论