问题

PostgreSQL 安装了 orafce 插件，发现BUG提交给社区插件作者，作者修复后如何更新到现有环境中？

案例

现象

在对 PostgreSQL 插件 orafce 进行测试的时候发现一个问题：https://www.modb.pro/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 进行测试的时候发现一个问题：https://www.modb.pro/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)

虽然对现有环境有影响，但依然膜拜大佬。