Re: Unable to create oracle_fdw (foreign data wrapper) extension

Thanks again, Scott. No success yet though.

I uninstalled Oracle instant client 12.1 using dpkg, downloaded RPMs for
instant client 10.2 and installed them with alien.

I also downloaded oracle_fdw 1.4 and installed it, since I was getting this
error with oracle_fdw 1.5:
---
postgres=# create extension oracle_fdw;
ERROR: could not load library "/usr/lib/postgresql/9.4/lib/oracle_fdw.so":
libclntsh.so.12.1: cannot open shared object file: No such file or directory
---

Once I installed oracle_fdw 1.4 I got this:

# ldd /usr/lib/postgresql/9.4/lib/oracle_fdw.so
linux-vdso.so.1 => (0x00007ffc3b5ec000)
libclntsh.so.10.1 => /usr/lib/oracle/10.2.0.3/client64/lib/libclntsh.so.10.1
(0x00007f7251471000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f72510e6000)
libnnz10.so => /usr/lib/oracle/10.2.0.3/client64/lib/libnnz10.so
(0x00007f7250c42000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f7250a3e000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f72507bc000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
(0x00007f725059f000)
libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f7250387000)
/lib64/ld-linux-x86-64.so.2 (0x00007f7252a3b000)

But again when I try to load the extension I get the same error and
Postgres crashes. When I look in the latest log file at
/var/lib/postgresql/9.4/main/pg_log I see these lines:

---
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOG: 00000: server process (PID
20397) was terminated by signal 11: Segmentation fault
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=DETAIL: Failed process was
running: create extension oracle_fdw;
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOCATION: LogChildExit,
postmaster.c:3347
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOG: 00000: terminating any
other active server processes
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOCATION: HandleChildCrash,
postmaster.c:3068
t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] WARNING:
57P02: terminating connection because of crash of another server process
t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] DETAIL:
The postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] HINT: In
a moment you should be able to reconnect to the database and repeat your
command.
t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] LOCATION:
quickdie, postgres.c:2581
t=2016-09-15 16:16:08 PDT d= h= p=19668 a=WARNING: 57P02: terminating
connection because of crash of another server process
---

On Thu, Sep 15, 2016 at 2:58 PM, Scott Mead <scottm(at)openscg(dot)com> wrote:

>
>
> On Thu, Sep 15, 2016 at 4:57 PM, Arun Rangarajan <arunrangarajan(at)gmail(dot)com
> > wrote:
>
>> Thanks, Scott.
>>
>> oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/
>>
>> Oracle client version: instantclient 12.1
>>
>
> I've had problems using anything > instant client 10. Give it a shot.
>
> --Scott
>
>
>
>>
>> /usr/lib/postgresql/9.4/lib# ldd oracle_fdw.so
>> linux-vdso.so.1 => (0x00007fff50744000)
>> libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1
>> (0x00007f44769f1000)
>> libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f4476666000)
>> libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so
>> (0x00007f4475f4f000)
>> libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so
>> (0x00007f4475d0b000)
>> libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f4475b07000)
>> libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f4475884000)
>> libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
>> (0x00007f4475668000)
>> libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f4475450000)
>> librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f4475247000)
>> libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f4475045000)
>> libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1
>> (0x00007f4474af5000)
>> /lib64/ld-linux-x86-64.so.2 (0x00007f447990c000)
>>
>>
>>
>> On Thu, Sep 15, 2016 at 1:10 PM, Scott Mead <scottm(at)openscg(dot)com> wrote:
>>
>>>
>>>
>>> On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <
>>> arunrangarajan(at)gmail(dot)com> wrote:
>>>
>>>> I am following the instructions here:
>>>> http://blog.dbi-services.com/connecting-your-postgresql-inst
>>>> ance-to-an-oracle-database/
>>>> to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL
>>>> server.
>>>>
>>>> ---
>>>> Oracle version: Oracle Database 12c Enterprise Edition Release
>>>> 12.1.0.2.0 - 64bit Production, running on Red Hat Linux 7.2
>>>>
>>>> PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu,
>>>> compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7
>>>> (wheezy).
>>>> ---
>>>>
>>>> I was able to install sqlplus and connect from PostgreSQL server to
>>>> Oracle server using sqlplus successfully, so connectivity is not a problem.
>>>>
>>>> But when I try to create the extension, I get the following error:
>>>>
>>>>
>>> ---
>>>> postgres=# create extension oracle_fdw;
>>>> server closed the connection unexpectedly
>>>> This probably means the server terminated abnormally
>>>> before or while processing the request.
>>>> The connection to the server was lost. Attempting reset: Failed.
>>>>
>>>
>>> Hmm, odd that it's causing a crash.
>>>
>>> Which version of the oracle_fdw and which version of the oracle
>>> libraries are you linked to? Make sure to check 'ldd oracle_fdw.so'
>>>
>>> --Scott
>>>
>>>
>>>
>>>> ---
>>>>
>>>> Then I took clues from https://github.com/dalibo/pg_qualstats/issues/1
>>>> and added oracle_fdw to shared_preload_libraries in postgresql.conf like
>>>> this:
>>>>
>>>> shared_preload_libraries = 'oracle_fdw'
>>>>
>>>> but now I can't restart Postgres:
>>>>
>>>> ---
>>>> # service postgresql restart
>>>> [....] Restarting PostgreSQL 9.4 database server: main[....] The
>>>> PostgreSQL server failed to start. Please check the log output:
>>>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache
>>>> ID[FAILt=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:
>>>> SearchSysCacheList, syscache.c:1219 ... failed!
>>>> failed!
>>>> ---
>>>>
>>>> Looking into /var/log/postgresql/postgresql-9.4-main.log I only see
>>>> these two lines:
>>>>
>>>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache
>>>> ID: 41
>>>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:
>>>> SearchSysCacheList, syscache.c:1219
>>>>
>>>> Removing oracle_fdw from shared_preload_libraries allows postgres to be
>>>> restarted, so this is the one causing restart to fail.
>>>>
>>>> How to fix this and get the foreign data wrapper working?
>>>>
>>>> Thank you.
>>>>
>>>> ​
>>>>
>>>
>>>
>>>
>>> --
>>> --
>>> Scott Mead
>>> Sr. Architect
>>> *OpenSCG <http://openscg.com>*
>>> http://openscg.com
>>>
>>
>>
>
>
> --
> --
> Scott Mead
> Sr. Architect
> *OpenSCG <http://openscg.com>*
> http://openscg.com
>