PostgreSQL插件--oracle_fdw

原創(chuàng) 張玉龍 2022-04-11

5426

oracle_fdw 的簡(jiǎn)介

PostgreSQL 的 oracle_fdw 擴(kuò)展是一個(gè)外部數(shù)據(jù)包裝器，允許您通過(guò)外部表訪問(wèn) Oracle 表和視圖（包括物化視圖）。
https://github.com/laurenz/oracle_fdw
當(dāng) PostgreSQL 客戶端訪問(wèn)外部表時(shí)，oracle_fdw 通過(guò) PostgreSQL 服務(wù)器上的 Oracle 調(diào)用接口 (OCI) 庫(kù)訪問(wèn)外部 Oracle 數(shù)據(jù)庫(kù)中的相應(yīng)數(shù)據(jù)。

oracle_fdw 使用限制

理論上可以在 PostgreSQL 和 Oracle 客戶端支持的任何平臺(tái)上編譯和運(yùn)行。
需要 PostgreSQL 9.3 或更高版本，但是不支持以下 PostgreSQL 版本：9.6.0 到 9.6.8 和 10.0 到 10.3。
需要 Oracle 客戶端版本 11.2 或更高版本。
支持的 Oracle 服務(wù)器版本取決于使用的客戶端版本。
IMPORT FOREIGN SCHEMA 不適用于 Oracle 服務(wù)器 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 的環(huán)境變量

-- 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 動(dòng)態(tài)庫(kù)

echo "/usr/lib/oracle/11.2/client64/lib" >> /etc/ld.so.conf
ldconfig

測(cè)試連接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 在環(huán)境變量 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
-- 使用超級(jí)用戶安裝插件
[root@pgtest2 ~]# psql -U postgres
postgres=# CREATE EXTENSION oracle_fdw with schema public;
CREATE EXTENSION

編譯安裝一個(gè)報(bào)錯(cuò)

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 環(huán)境變量不起作用，還是在 /etc/ld.so.conf 中加上 Oracle 的動(dòng)態(tài)庫(kù)吧

echo "/usr/lib/oracle/11.2/client64/lib" >> /etc/ld.so.conf
ldconfig

檢查插件的安裝
需要注意，psql 命令 \dx 顯示的擴(kuò)展插件版本不是 oracle_fdw 的安裝版本，要獲取 oracle_fdw 版本，請(qǐng)使用函數(shù) 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 訪問(wèn) Oracle 數(shù)據(jù)庫(kù)，需要指定以下設(shè)置。
創(chuàng)建外部服務(wù)器 SERVER ，配置 Oracle 的連接

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.0.51:1521/orcl');
-- oradb： 自定義一個(gè) SERVER NAME
-- OPTIONS: 有3個(gè)參數(shù)--dbserver、isolation_level、nchar
  -- dbserver(必需)：定義連接 Oracle 數(shù)據(jù)庫(kù)的連接字符串。
  -- isolation_level(可選，默認(rèn)為 serializable): 在 Oracle 數(shù)據(jù)庫(kù)中使用的事務(wù)隔離級(jí)別，可設(shè)置的參數(shù)值 serializable、read_committed、read_only。
  -- nchar(可選，默認(rèn)為 off): 是否開(kāi)啟 Oracle 端的字符轉(zhuǎn)換，這個(gè)參數(shù)的開(kāi)啟對(duì)性能有很大影響。

-- 這個(gè) SERVER 可以賦權(quán)給普通用戶使用
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)

可以使用普通用戶(超級(jí)用戶也沒(méi)問(wèn)題)創(chuàng)建 PostgreSQL 和 Oracle 之間的用戶映射

CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'system', password 'oracle');
-- postgres： PostgreSQL 中已存在的用戶
-- oradb： 已創(chuàng)建的 SERVER NAME
-- OPTIONS： 有2個(gè)參數(shù)--user、password
  -- user(必需)：Oracle 用戶名
  -- password(必需)：Oracle 用戶的密碼

-- 查看，密碼會(huì)存儲(chǔ)在數(shù)據(jù)庫(kù)中
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 表里面有一條數(shù)據(jù)
SQL> select EMPNO,ENAME,DEPTNO from emp_list;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      1111 aaaa               10

PostgreSQL 端創(chuàng)建外部表

想要在 PostgreSQL 中操作 Oracle 的 emp_list 表，首先在 PostgreSQL 中創(chuàng)建外部表，外部表指向 Oracle 的 emp_list 表，外部表有兩種創(chuàng)建方式，任選其一

PostgreSQL 外部表創(chuàng)建方式一，CREATE FOREIGN TABLE
需要注意：
- 外部表的字段名不需要與 Oracle 的表保持一致，但是字段順序需要與 Oracle 的表保持一致
- 外部表的主鍵需要與 Oracle 的表保持一致
- OPTIONS 屬性里的 schema 和 table 名稱必須大寫，不然 PostgreSQL 端操作外部表會(huì)報(bào) ‘ORA-00942: table or view does not exist’。
- OPTIONS 屬性里的 schema 和 table 必須用單引號(hào)
- 必須定義 oracle_fdw 可以轉(zhuǎn)換的列
  
  如果數(shù)據(jù)的長(zhǎng)度超過(guò)了實(shí)際的列長(zhǎng)度，就會(huì)出現(xiàn)運(yùn)行時(shí)錯(cuò)誤。另外請(qǐng)注意，數(shù)據(jù)類型的行為可能不同，例如浮點(diǎn)數(shù)據(jù)類型和日期時(shí)間數(shù)據(jù)類型中的分?jǐn)?shù)舍入。
  請(qǐng)記住，默認(rèn)情況下，Oracle 中 CHAR 和 VARCHAR2 類型的長(zhǎng)度以字節(jié)為單位指定，而 PostgreSQL 的 CHAR、VARCHAR 和 TEXT 類型的長(zhǎng)度以字符為單位指定。

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： 已創(chuàng)建的 SERVER NAME
-- 外部表 OPTIONS: 有7個(gè)參數(shù)--table、dblink、schema、max_long、readonly、sample_percent、prefetch
  -- table(必需)：Oracle 端的表名，必須大寫，也可以自定義查詢，后面有示例
  -- dblink(可選): 訪問(wèn)表所需的 Oracle 端的 database link，必須大寫
  -- schema(可選): Oracle 端的表所屬用戶，適用于訪問(wèn)不屬于連接 Oracle 用戶的表，必須大寫
  -- max_long(可選，默認(rèn)是 '32767'): Oracle 表中 LONG、LONG RAW 和 XMLTYPE 列的最大長(zhǎng)度。如果 max_long 小于檢索到的最長(zhǎng)值的長(zhǎng)度，您將收到錯(cuò)誤消息 ORA-01406: fetched column value was truncated。
  -- readonly(可選，默認(rèn)是 'false'): 僅在此選項(xiàng)未設(shè)置為 yes/on/true 的表上才允許 DML 操作。
  -- sample_percent(可選，默認(rèn)是 '100'): 此選項(xiàng)僅影響 ANALYZE 處理，可用于在合理的時(shí)間內(nèi) ANALYZE 非常大的表。
  -- prefetch(可選，默認(rèn)是 '200'): 設(shè)置在外部表掃描期間通過(guò) PostgreSQL 和 Oracle 之間的單次往返獲取的行數(shù)。該值必須介于 0 和 10240 之間，其中零值禁用預(yù)讀。
-- 列 OPTIONS: 有2個(gè)參數(shù)--key、strip_zeros
  -- key(可選，默認(rèn)是 'false'): 如果設(shè)置為 yes/on/true，則外部 Oracle 表上的相應(yīng)列被視為主鍵列。要使 UPDATE 和 DELETE 起作用，您必須在屬于表主鍵的所有列上設(shè)置此選項(xiàng)。
  -- strip_zeros(可選，默認(rèn)是 'false'): 如果設(shè)置為 yes/on/true，ASCII 0 字符將在傳輸過(guò)程中從字符串中刪除。此類字符在 Oracle 中有效，但在 PostgreSQL 中無(wú)效，因此在被 oracle_fdw 讀取時(shí)會(huì)導(dǎo)致錯(cuò)誤。character此選項(xiàng)僅對(duì),character varying 和 text 列有意義。

PostgreSQL 外部表創(chuàng)建方式二，IMPORT FOREIGN SCHEMA
需要注意：
- 這種方式不需要指定表結(jié)構(gòu)，但是外部表名需要一致，也就是當(dāng)前 PostgreSQL 的 SCHEMA 下不能存在同名表，否則創(chuàng)建失敗。
- 從 PostgreSQL 9.5 開(kāi)始，支持 IMPORT FOREIGN SCHEMA 為 Oracle 模式中的所有表批量導(dǎo)入表定義。
- IMPORT FOREIGN SCHEMA 將為在 ALL_TAB_COLUMNS 中找到的所有對(duì)象創(chuàng)建外部表。這包括表、視圖和物化視圖，但不包括同義詞。
- Oracle SCHEMA 名稱通常為大寫。由于 PostgreSQL 在處理之前將名稱轉(zhuǎn)換為小寫，因此您必須用雙引號(hào)保護(hù) SCHEMA 名稱（例如"SCOTT"）。
- LIMIT TO 導(dǎo)入括號(hào)內(nèi)包含的表，多個(gè)表以逗號(hào)分隔，EXCEPT 導(dǎo)入不包含(排除)括號(hào)內(nèi)的表，多個(gè)表以逗號(hào)分隔

-- 導(dǎo)入單表
IMPORT FOREIGN SCHEMA "SCOTT" limit to (EMP) from server oradb into public;

-- 導(dǎo)入多表
IMPORT FOREIGN SCHEMA "SCOTT" LIMIT TO (EMP,DEPT) from server oradb into public OPTIONS (readonly 'true', prefetch '100');

-- 排除表導(dǎo)入
IMPORT FOREIGN SCHEMA "SCOTT" EXCEPT (EMP,DEPT) from server oradb into public;

-- IMPORT FOREIGN SCHEMA 支持的選項(xiàng)：
  -- case(默認(rèn)：smart)：控制導(dǎo)入期間表名和列名的大小寫，參數(shù)值：
           -- keep: 保留 Oracle 中的名稱，通常為大寫。
           -- lower: 將所有表名和列名轉(zhuǎn)換為小寫。
           -- smart: 僅轉(zhuǎn)換 Oracle 中全部大寫的名稱。
  -- collat??ion(默認(rèn)：default)：用于 case 選項(xiàng)的 lower 和 smart 選項(xiàng)的排序規(guī)則
  -- dblink
  -- readonly
  -- max_long
  -- sample_percent
  -- prefetch

查看已創(chuàng)建的外部表信息

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 外部表是否可以只關(guān)聯(lián) Oracle 表的某幾個(gè)字段呢？比如示例中的 emp_list 表有8個(gè)字段，我只想關(guān)聯(lián)3個(gè)字段（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)');

-- 在這種情況下不要設(shè)置 schema 選項(xiàng)。

postgres=# select * from ora_emp_list;
 empno | ename  | deptno
-------+--------+--------
  7782 | CLARK  |     10
  7839 | KING   |     10
  7934 | MILLER |     10
... ...

PostgreSQL 外部表的創(chuàng)建也可以實(shí)現(xiàn)自定義查詢

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

-- 以下實(shí)現(xiàn)自定義查詢創(chuàng)建 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'')');

-- 在這種情況下不要設(shè)置 schema 選項(xiàng)。

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)

修改外部表屬性

-- 關(guān)閉外部表的只讀屬性
postgres=# alter foreign table emp OPTIONS (SET readonly 'no');

訪問(wèn) Oracle 數(shù)據(jù)庫(kù)

客戶端發(fā)送對(duì)外部表的查詢
PostgreSQL 請(qǐng)求 oracle_fdw 獲取 Oracle 表的執(zhí)行計(jì)劃和表數(shù)據(jù)
oracle_fdw 讀取外部服務(wù)器和用戶映射信息并返回訪問(wèn)信息
oracle_fdw 通過(guò) OCI 庫(kù)將查詢發(fā)送到 Oracle 數(shù)據(jù)庫(kù)
oracle_fdw 獲取結(jié)果返回給 PostgreSQL
客戶端收到結(jié)果

postgres=# select * from dept;
 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

-- 查看執(zhí)行計(jì)劃，執(zhí)行計(jì)劃顯示 正在訪問(wèn) 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 開(kāi)頭的行顯示在 Oracle 端訪問(wèn)的表。
-- 以 Oracle query 開(kāi)頭的行顯示了在 Oracle 端執(zhí)行的 SQL 語(yǔ)句（因?yàn)槲覀冎付薃NALYZE）
-- 以 Oracle plan 開(kāi)頭的行顯示了 Oracle 端的執(zhí)行計(jì)劃（因?yàn)槲覀冎付?VERBOSE）

DML 操作

PostgreSQL 在外部表做 DML 操作，也會(huì)直接作用到 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 表的字段名，對(duì) PostgreSQL 的外部表無(wú)影響

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 的函數(shù)

-- oracle_fdw 創(chuàng)建的函數(shù)
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 是創(chuàng)建外部數(shù)據(jù)包裝器所必需的處理程序和驗(yàn)證器函數(shù)。
FOREIGN DATA WRAPPER oracle_fdw
  HANDLER oracle_fdw_handler
  VALIDATOR oracle_fdw_validator

函數(shù) oracle_close_connections

oracle_fdw 會(huì)緩存 Oracle 連接，因?yàn)闉槊總€(gè)單獨(dú)的查詢創(chuàng)建 Oracle 會(huì)話的成本很高。當(dāng) PostgreSQL 會(huì)話結(jié)束時(shí)，所有連接都會(huì)自動(dòng)關(guān)閉。
函數(shù) oracle_close_connections() 可用于關(guān)閉所有緩存的 Oracle 連接，但是不能在修改 Oracle 數(shù)據(jù)的事務(wù)中調(diào)用此函數(shù)。

-- 在 Oracle 數(shù)據(jù)庫(kù)中可以看到有一個(gè) INACTIVE 的會(huì)話連接
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

-- 此會(huì)話上次執(zhí)行的 SQL_ID 是 bt3snu1v4nrwy，可以看到這個(gè) 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 端使用函數(shù) oracle_close_connections() 清理 oracle_fdw 緩存的 Oracle 連接。
postgres=# select oracle_close_connections();
 oracle_close_connections
--------------------------

(1 row)

-- 在 Oracle 數(shù)據(jù)庫(kù)中再次查詢會(huì)話連接情況，就看不到 oracle_fdw 的連接會(huì)話。
SQL> select SADDR,SID,USERNAME,STATUS,OSUSER,MACHINE,TYPE,SQL_ID,LOGON_TIME,PREV_SQL_ID from v$session where type='USER';

函數(shù) oracle_diag

此函數(shù)僅用于診斷目的。
它將返回 oracle_fdw、PostgreSQL 服務(wù)器和 Oracle 客戶端的版本。如果調(diào)用時(shí)不帶參數(shù)或者指定 NULL ，它將額外返回一些用于建立 Oracle 連接的環(huán)境變量的值。
如果調(diào)用時(shí)指定外部服務(wù)器的名稱，它將額外返回 Oracle 服務(wù)器版本。

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)

函數(shù) oracle_execute

該函數(shù)可用于在遠(yuǎn)程 Oracle 服務(wù)器上執(zhí)行任意 SQL 語(yǔ)句。這僅適用于不返回結(jié)果的語(yǔ)句（通常是 DDL 語(yǔ)句）。
使用該函數(shù)時(shí)要小心，因?yàn)樗赡軙?huì)干擾 oracle_fdw 的事務(wù)管理。請(qǐng)記住，在 Oracle 中運(yùn)行 DDL 語(yǔ)句將發(fā)出隱式 COMMIT。最好建議您在多版本并發(fā)事務(wù)之外使用此功能。

-- 注意 SQL 語(yǔ)句結(jié)尾不要加分號(hào)
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

下推

外部數(shù)據(jù)包裝器(Foreign data wrappers)采用一種稱為 pushdown 的機(jī)制，它允許遠(yuǎn)程端執(zhí)行 WHERE、ORDER BY 和 JOIN 子句。下推 WHERE 和 JOIN 減少了本地和遠(yuǎn)程服務(wù)器之間傳輸?shù)臄?shù)據(jù)量，避免了網(wǎng)絡(luò)通信瓶頸。

WHERE 下推

如果 SQL 語(yǔ)句有 WHERE 子句，則將查詢條件傳遞給 Oracle 數(shù)據(jù)庫(kù)執(zhí)行，包括其中調(diào)用的任何函數(shù)。

postgres=# EXPLAIN ANALYZE VERBOSE select ename,sal from emp where sal <= 2000;

ORDER BY 下推

一般情況下，如果SQL語(yǔ)句有ORDER BY子句不包含字符類型列，就會(huì)被下推，數(shù)據(jù)會(huì)在Oracle端進(jìn)行排序。

如果該子句包含字符類型的列，則不會(huì)下推，因?yàn)?oracle_fdw 不能保證 Oracle 和 PostgreSQL 的排序順序相同。
如果可以保證兩邊的排序順序相同，則該子句將被下推，數(shù)字和日期時(shí)間數(shù)據(jù)類型就是這種情況，對(duì)于其他數(shù)據(jù)類型，需要單獨(dú)驗(yàn)證操作。
如果語(yǔ)句還包含 JOIN，則不會(huì)下推該子句。

排序字段是數(shù)值類型–下推

排序字段是時(shí)間類型–下推

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

幾個(gè)函數(shù)的測(cè)試

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 是否被下推

請(qǐng)注意以下對(duì) JOIN 子句下推的限制：

JOIN 子句子句必須指定在 SELECT 語(yǔ)句中
要連接的表必須在同一個(gè)外部服務(wù)器上定義
它必須只涉及 2 個(gè)表 - 附加表的連接將在 PostgreSQL 端執(zhí)行
如果 SELECT 語(yǔ)句包含 JOIN 和 WHERE，則兩者都被下推
如果 SELECT 語(yǔ)句包含 JOIN 和 ORDER BY，那么只有 JOIN 被下推
沒(méi)有連接條件的交叉連接不會(huì)下推該子句

JOIN 三個(gè)外部表

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 端執(zhí)行表 t1 和 t3 的連接
表 t1 和 t2 的聯(lián)接被下推到 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 沒(méi)有被下推

更新事務(wù)和序列化錯(cuò)誤

oracle_fdw 還支持更新事務(wù)，使用 SERIALIZABLE 事務(wù)隔離級(jí)別來(lái)確保一致性。這是因?yàn)閱蝹€(gè) SQL 語(yǔ)句可能會(huì)向 Oracle 數(shù)據(jù)庫(kù)生成多個(gè) SQL 語(yǔ)句。因此，使用多個(gè)并發(fā)事務(wù)更新外部表可能會(huì)導(dǎo)致序列化錯(cuò)誤。

為避免這種情況，請(qǐng)確保應(yīng)用程序不會(huì)同時(shí)更新外部表。如果發(fā)生序列化錯(cuò)誤，則回滾事務(wù)并再次執(zhí)行。

oracle_fdw 不支持預(yù)準(zhǔn)備語(yǔ)句（PREPARE）和兩階段提交（PREPARE TRANSACTION 等），因?yàn)樗鼈冃枰刂?Oracle 數(shù)據(jù)庫(kù)。


-- 第一個(gè)會(huì)話執(zhí)行 update 不提交，當(dāng)前會(huì)話可以看到更改后的數(shù)據(jù)
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)

-- 第二個(gè)會(huì)話和在Oracle 上均查詢不到 第一個(gè)會(huì)話 未提交的數(shù)據(jù)
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

-- 在第二個(gè)會(huì)話上執(zhí)行 update 更新同一條記錄，被堵塞
postgres=# begin;
BEGIN
postgres=*# update emp set sal=2000 where empno=7369;

-- 在 Oracle 也執(zhí)行 update 更新同一條記錄，也被堵塞
SQL> update scott.emp set sal=2000 where empno=7369;

-- 第一個(gè)會(huì)話 提交事務(wù)
postgres=*# commit;
COMMIT

-- 第二個(gè)會(huì)話報(bào)錯(cuò)ORA-08177，事務(wù)終止并回滾，只能重啟事務(wù)
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

外部表定義約束和默認(rèn)值

創(chuàng)建外部表時(shí)，建議匹配遠(yuǎn)程表(Oracle上的表)中定義的約束（CHECK、NOT NULL 等）和默認(rèn)值（DEFAULT）。
雖然 oracle_fdw 將默認(rèn)值應(yīng)用于使用 DEFAULT 創(chuàng)建的列，但它不檢查約束，約束是在 Oracle 端檢查。

oracle_fdw 不檢查約束

在下面的示例中，我們嘗試為 emp_id 列（在 Oracle 中使用 NOT NULL 創(chuàng)建）和 dept 列（在 PostgreSQL 和 Oracle 中使用 DEFAULT 10 創(chuàng)建）插入一個(gè)具有 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)

在發(fā)送語(yǔ)句之前，oracle_fdw 將 dept 列的 ‘default’ 替換為 ‘10’，但它不驗(yàn)證 emp-id 列的 NULL 約束，并按原樣發(fā)送值。這將導(dǎo)致 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")

如果主鍵指定錯(cuò)誤

即使你沒(méi)有在外表上定義約束來(lái)匹配 Oracle 表，只要 Oracle 端沒(méi)有違反約束，SQL 語(yǔ)句也不會(huì)返回錯(cuò)誤。

在下面的示例中，我們創(chuàng)建了一個(gè)表并錯(cuò)誤地將 emp_id 指定為主鍵，而不是 id。
之后，我們執(zhí)行 2 次 INSERT，將相同的值 ‘9’ 添加到不正確的主鍵中，這不會(huì)產(chǎn)生錯(cuò)誤，因?yàn)榫瓦h(yuǎn)程 Oracle 表而言，對(duì) emp_id 的唯一約束是 NOT NULL。
但是，稍后當(dāng)我們嘗試執(zhí)行 UPDATE 和 DELETE 時(shí)，我們收到一個(gè)錯(cuò)誤，因?yàn)楝F(xiàn)在我們?cè)谕獗砩系闹麈I約束之間存在沖突。

-- PostgreSQL 創(chuàng)建外部表，錯(cuò)誤的指定了主鍵列
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)

-- 插入數(shù)據(jù)
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
-- 沒(méi)有檢查外部表的主鍵約束，所以插入了行
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)
-- 現(xiàn)在對(duì)于外部表是存在主鍵沖突的
-- UPDATE 和 DELETE 失敗并出現(xiàn) oracle_fdw 錯(cuò)誤
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.

如果未指定默認(rèn)值

在下面的示例中，我們創(chuàng)建了一個(gè)表并錯(cuò)誤地指定了沒(méi)有默認(rèn)值 10 的 dept 列。然后，我們?yōu)?dept 列插入了一個(gè)指定“默認(rèn)”的行 - 因?yàn)橥獠勘頉](méi)有指定默認(rèn)值，oracle-fdw 會(huì)將其替換為 NULL 并將其發(fā)送給 Oracle。由于在 Oracle 端未使用 NOT NULL 指定該列，因此該語(yǔ)句將成功創(chuàng)建在 dept 列上具有 NULL 的行，這不是預(yù)期的結(jié)果。

-- PostgreSQL 創(chuàng)建外部表，dept 列未指定默認(rèn)值
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 值，這不是預(yù)期的結(jié)果
postgres=# select * from f_ora_tbl3 where id=10;
 id | emp_id | name | dept
----+--------+------+------
 10 |    100 | abc  |
(1 row)

----------------end

參考文獻(xiàn)

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

墨力計(jì)劃 postgresql

「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」

關(guān)注作者

【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來(lái)源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。

特色一级强游戏,海奥华预言免费阅读,51漫画兑换码,美女裸体无遮挡永久免费观看网站,lubuntu线路检测入口