問題

PostgreSQL 安裝了 orafce 插件，發現BUG提交給社區插件作者，作者修復后如何更新到現有環境中？

案例

現象

在對 PostgreSQL 插件 orafce 進行測試的時候發現一個問題：http://m.sunline.cc/db/388853 《SUBSTRB 函數》。

SUBSTRB 函數對于負值的起始位置返回錯誤的結果

postgres=# SELECT SUBSTRB('aaabbbccc',4,3) FROM DUAL;
 substrb
---------
 bbb
(1 row)

-- 以下這個返回的結果應該是錯誤的
postgres=# SELECT SUBSTRB('aaabbbccc',-2,6) FROM DUAL;
 substrb
---------
 aaa
(1 row)

-- oracle 的結果是 cc
SQL> SELECT SUBSTRB('aaabbbccc',-2,6) FROM DUAL;

SU
--
cc

問題提交社區

https://github.com/orafce/orafce/issues/172

將這個問題提交到 GitHub 社區，插件作者很快就給了回復并進行了修復，不得不說這位大佬的效率是真的快，給大佬點贊。

下載源代碼，編譯安裝更新插件

[root@pgtest1 ~]# cd /enmo/soft/
[root@pgtest1 soft]# unzip orafce-master.zip
[root@pgtest1 soft]# cd orafce-master
[root@pgtest1 orafce-master]# make
[root@pgtest1 orafce-master]# make install
[root@pgtest1 orafce-master]# psql -c "ALTER EXTENSION orafce UPDATE;"

查看效果

postgres=# SELECT SUBSTRB('aaabbbccc',-2,6) FROM DUAL;
 substrb
---------
 cc
(1 row)

再次膜拜大佬，為大佬的技術狂熱精神點贊。

不幸的情況

如果遇到不幸運的情況，就得需要刪除插件再重新安裝，但是刪除 orafce 插件，相關聯的字段也都需要刪除，這種更新如果在生產上是比較致命的。

不幸的案例現象

在對 PostgreSQL 插件 orafce 進行測試的時候發現一個問題：http://m.sunline.cc/db/388853 《NVL2 函數》。

drop table tt;
create table tt (col1 int,col3 int);
insert into tt values (1001,1000),(1002,2000),(2002,null),(3001,3000);

-- oracle
SQL> SELECT col1, NVL2(col3,'IS NOT NULL','IS NULL') FROM tt;

      COL1 NVL2(COL3,'
---------- -----------
      1001 IS NOT NULL
      1002 IS NOT NULL
      2002 IS NULL
      3001 IS NOT NULL

-- orafce number is ok
postgres=# SELECT col1, NVL2(col3,0,1) FROM tt;
 col1 | nvl2
------+------
 1001 |    0
 1002 |    0
 2002 |    1
 3001 |    0
(4 rows)

-- 返回數值類型的沒問題，返回字符類型有問題
postgres=# SELECT col1, NVL2(col3,'IS NOT NULL','IS NULL') FROM tt;
ERROR:  invalid input syntax for type integer: "IS NOT NULL"
LINE 1: SELECT col1, NVL2(col3,'IS NOT NULL','IS NULL') FROM tt;
                               ^

問題提交社區

https://github.com/orafce/orafce/issues/173

下載源代碼，編譯安裝更新插件

• 嘗試更新一下插件，沒用

postgres=# \dx
                                                       List of installed extensions
        Name        | Version |   Schema   |                                          Description
--------------------+---------+------------+-----------------------------------------------------------------------------------------------
 orafce             | 3.19    | public     | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# ALTER EXTENSION orafce UPDATE;
NOTICE:  version "3.19" of extension "orafce" is already installed
ALTER EXTENSION
postgres=# \dx
                                                       List of installed extensions
        Name        | Version |   Schema   |                                          Description
--------------------+---------+------------+-----------------------------------------------------------------------------------------------
 orafce             | 3.19    | public     | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# SELECT col1, NVL2(col3,'IS NOT NULL','IS NULL') FROM tt;
ERROR:  invalid input syntax for type integer: "IS NOT NULL"
LINE 1: SELECT col1, NVL2(col3,'IS NOT NULL','IS NULL') FROM tt;
                               ^

• 那就刪除重裝吧

postgres=# DROP EXTENSION orafce;
ERROR:  cannot drop extension orafce because other objects depend on it
DETAIL:  table test_range depends on type date
column hiredate of table emp depends on type date
column hire_date of table employees depends on type date
column end_date of table job_history depends on type date
column start_date of table job_history depends on type date
column loc of table dept depends on type varchar2
column dname of table dept depends on type varchar2
column job of table emp depends on type varchar2
column ename of table emp depends on type varchar2
column job of table bonus depends on type varchar2
column ename of table bonus depends on type varchar2
column country_name of table countries depends on type varchar2
column department_name of table departments depends on type varchar2
column job_id of table employees depends on type varchar2
column phone_numeric of table employees depends on type varchar2
column email of table employees depends on type varchar2
column last_name of table employees depends on type varchar2
view emp_view depends on column last_name of table employees
column first_name of table employees depends on type varchar2
materialized view mview_tt depends on column first_name of table employees
column job_title of table jobs depends on type varchar2
column job_id of table jobs depends on type varchar2
column job_id of table job_history depends on type varchar2
column state_province of table locations depends on type varchar2
column city of table locations depends on type varchar2
column postal_code of table locations depends on type varchar2
column street_address of table locations depends on type varchar2
column region_name of table regions depends on type varchar2
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=# DROP EXTENSION orafce CASCADE;
postgres=# create EXTENSION orafce;
postgres=# create table tt (col1 int,col3 int);
postgres=# insert into tt values (1001,1000),(1002,2000),(2002,null),(3001,3000);
postgres=# SELECT col1, NVL2(col3,'IS NOT NULL','IS NULL') FROM tt;
 col1 |    nvl2
------+-------------
 1001 | IS NOT NULL
 1002 | IS NOT NULL
 2002 | IS NULL
 3001 | IS NOT NULL
(4 rows)

雖然對現有環境有影響，但依然膜拜大佬。