postgresql插件-m6米乐安卓版下载

原创张玉龙 2022-04-11

2402

postgresql 的 oracle_fdw 扩展是一个外部数据包装器，允许您通过外部表访问 oracle 表和视图（包括物化视图）。
当 postgresql 客户端访问外部表时，oracle_fdw 通过 postgresql 服务器上的 oracle 调用接口 (oci) 库访问外部 oracle 数据库中的相应数据。

理论上可以在 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客户端下载地址
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
米乐app官网下载 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>

确保 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 数据库，需要指定以下设置。
创建外部服务器 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 创建的函数
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

墨力计划 postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者