Unable to create oracle_fdw (foreign data wrapper) extension

From: Arun Rangarajan <arunrangarajan(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unable to create oracle_fdw (foreign data wrapper) extension
Date: 2016-09-15 19:25:44
Message-ID: CAOnbPhSzrNLaupEP8UeaG+MM-ScRMUh0XNvkMuEYZsYzPEKEzg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.
---

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.


Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2016-09-15 19:29:32 Re: PgSQL versions supported on ubuntu 16 and debian 8
Previous Message Jaap Roes 2016-09-15 19:13:40 Re: PostgreSQL GIN index not used when ts_query language is fetched from a column