[YSQL] Support orafce extension

[ddorian]

Investigate https://github.com/orafce/orafce which adds oracle-db functions to pg.

Install instructions (ubuntu 18.04):

sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev
sudo apt-get install postgresql-11
sudo apt-get install postgresql-server-dev-11
git clone [email protected]:orafce/orafce.git
cd orafce
make all
sudo make install

This will install it for the local postgresql. Make sure pg_config is in the path. Then:

sudo make install
/bin/mkdir -p '/usr/lib/postgresql/11/lib'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-11/extension'
/usr/bin/install -c -m 755  orafce.so '/usr/lib/postgresql/11/lib/orafce.so'
/usr/bin/install -c -m 644 .//orafce.control '/usr/share/postgresql/11/extension/'
/usr/bin/install -c -m 644 .//orafce--3.8.sql .//orafce--3.2--3.3.sql .//orafce--3.3--3.4.sql .//orafce--3.4--3.5.sql .//orafce--3.5--3.6.sql .//orafce--3.6--3.7.sql .//orafce--3.7--3.8.sql  '/usr/share/postgresql/11/extension/'
/usr/bin/install -c -m 644 .//README.asciidoc .//COPYRIGHT.orafce .//INSTALL.orafce '/usr/share/doc/postgresql-doc-11/extension/'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode/orafce'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode'/orafce/
/usr/bin/install -c -m 644 parse_keyword.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 convert.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 file.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 datefce.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 magic.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 others.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 plvstr.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 plvdate.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 shmmc.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 plvsubst.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 utility.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 plvlex.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 alert.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 pipe.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 sqlparse.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 putline.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 assert.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 plunit.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 random.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 aggregate.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 orafce.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 varchar2.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 nvarchar2.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 charpad.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
/usr/bin/install -c -m 644 charlen.bc '/usr/lib/postgresql/11/lib/bitcode'/orafce/./
cd '/usr/lib/postgresql/11/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o orafce.index.bc orafce/parse_keyword.bc orafce/convert.bc orafce/file.bc orafce/datefce.bc orafce/magic.bc orafce/others.bc orafce/plvstr.bc orafce/plvdate.bc orafce/shmmc.bc orafce/plvsubst.bc orafce/utility.bc orafce/plvlex.bc orafce/alert.bc orafce/pipe.bc orafce/sqlparse.bc orafce/putline.bc orafce/assert.bc orafce/plunit.bc orafce/random.bc orafce/aggregate.bc orafce/orafce.bc orafce/varchar2.bc orafce/nvarchar2.bc orafce/charpad.bc orafce/charlen.bc

It will put a lot of files in postgresql dirs. Next steps are to copy them from there to yugabyte-db postgresql dir.

Next:

yugabyte=# create extension orafce;
ERROR:  ALTER FUNCTION not supported yet
HINT:  See https://github.com/YugaByte/yugabyte-db/issues/2717. Click '+' on the description to raise its priority

Go edit orafce--3.8.sql and comment all ALTER FUNCTION statements. Try again (and wait some minutes):

yugabyte=# create extension orafce;
CREATE EXTENSION

[ddorian]

All tests pass except the findings below.
Errors:

1. view oracle.product_componenent_version doesn't exist
2. select pos,token from plvlex.tokens('select * from a.b.c join d ON x=y', true, true); crashes the connection
3. Needs [YSQL] Feature Support - CREATE TABLE #1129 [YSQL] DEFERRABLE constraint should be supported #1709

ERROR:  INITIALLY DEFERRED constraint not supported yet
LINE 1: ...IGGER ora_alert_signal AFTER INSERT ON ora_alerts INITIALLY ...
                                                             ^
HINT:  See https://github.com/YugaByte/yugabyte-db/issues/1129. Click '+' on the description to raise its priority
QUERY:  CREATE CONSTRAINT TRIGGER ora_alert_signal AFTER INSERT ON ora_alerts INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE dbms_alert.defered_signal()

4. SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce.txt'); crashes the connection
5. dbms_output crashes the server
6. select * from dbms_pipe.db_pipes; crashes after a pipe has been created (though pipe send/get works)

Some tests need to be modified :

1. CREATE DATABASE regression_sort WITH TEMPLATE = template0 ENCODING='SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C';

[tylarb]

I was able to install orafce using the pg_config provided by yugabyte.

Replace podman with docker if you need.

podman run -d --rm --name yugabyte  -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042 yugabytedb/yugabyte:latest bin/yugabyted start --daemon=false --ui=false
podman exec -it yugabyte bash
yum install -y vim git make centos-release-scl openssl-devel 
yum install -y devtoolset-7-gcc* devtoolset-7-libatomic-devel
git clone https://github.com/orafce/orafce.git
git clone https://github.com/yugabyte/yugabyte-db.git
mkdir -p /opt/builds
ln -s /home/yugabyte/yugabyte-db /opt/builds
scl enable devtoolset-7 bash
export YB_SRC_ROOT=/home/yugabyte/yugabyte-db
export YB_IS_THIRDPARTY_BUILD=1
export PATH=/home/yugabyte/postgres/bin/:$PATH
export LD_LIBRARY_PATH=/home/yugabyte/postgres/lib/:$PATH
cd orafce
make
make install

ysqlsh

>>> create extension orafce;

Notes:

• I added postgres/bin to the path, as well as /home/yugabyte/bin to get ysqlsh
• LD_LIBRARY_PATH needs postgres/lib folder as well
• It would be great if the build process, requiring wrapped gcc, etc, were not necessary. Even if a base gcc version was required (in this case, I'm installing gcc-7 from scl)

[ramsrivatsa]

Orface is now integrated with YB with this commit 7a3c383.

[shaunstoltz]

See this pr #9576, may solve your issue more generally