Unable to create oracle_fdw (foreign data wrapper) extension

On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <arunrangarajan@gmail.com>
wrote:

I am following the instructions here:
http://blog.dbi-services.com/connecting-your-postgresql-
instance-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&gt;*
http://openscg.com

Thanks, Scott.

oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/

Oracle client version: instantclient 12.1

/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@openscg.com> wrote:

On Thu, Sep 15, 2016 at 4:57 PM, Arun Rangarajan <arunrangarajan@gmail.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@openscg.com> wrote:

On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <
arunrangarajan@gmail.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&gt;*
http://openscg.com

--
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com&gt;*
http://openscg.com

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@openscg.com> wrote:

Arun Rangarajan wrote:

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.

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

Well, as I told you, get a stack trace with debugging symbols.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Laurenz,

Thanks for your reply.

Sorry for the double posting here and StackOverflow
http://stackoverflow.com/questions/39518417/unable-to-create-oracle-fdw-extension-on-postgres
.

I will update the details on StackOverflow since formatting and editing are
easier there.

On Fri, Sep 16, 2016 at 3:14 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:

On 16.09.2016 17:01, Arun Rangarajan wrote:

Hi Laurenz,

Thanks for your reply.

Sorry for the double posting here and StackOverflow
http://stackoverflow.com/questions/39518417/unable-to-create-oracle-fdw-extension-on-postgres .

I will update the details on StackOverflow since formatting and editing are easier there.

On Fri, Sep 16, 2016 at 3:14 AM, Albe Laurenz <laurenz.albe@wien.gv.at <mailto:laurenz.albe@wien.gv.at>> wrote:

Arun Rangarajan wrote:

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.

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

Well, as I told you, get a stack trace with debugging symbols.

Yours,
Laurenz Albe

Hi!
May be this helps:

"Don't add oracle_fdw to shared_preload_libraries! (c) Laurenz Albe

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general