The oracle_fdw module is a
Postgres Pro extension that provides
a Foreign Data Wrapper for easy and efficient access to
Oracle databases, including
pushdown of WHERE conditions and required
columns as well as comprehensive EXPLAIN support.
The oracle_fdw extension is provided with
Postgres Pro Standard as a separate pre-built package
oracle-fdw-std-16
(for the detailed installation instructions, see
Chapter 16). oracle_fdw
requires Oracle Instant Client version 19.5.
For RPM-based systems (RHEL, SUSE, Red OS, ROSA, ALT Linux), download the
oracle-instantclient RPM and install it using
rpm. If you use a Debian-based system (Ubuntu, Astra
Linux), you have to either convert the RPM package to a Debian package
using alien, and then install it using
dpkg, or download the ZIP archive of the client
and extract the contents of the archive into the
/opt/oracle directory.
Once you have Postgres Pro Standard installed, create the oracle_fdw extension:
CREATE EXTENSION oracle_fdw;
That will define the required functions and create a foreign data wrapper.
Note that the extension version as shown by the
psql \dx command or the
system catalog pg_available_extensions is
not the installed version of
oracle_fdw. To get the
oracle_fdw version, use the
oracle_diag function.
oracle_fdw sets the MODULE
of the Oracle session to
postgres and the ACTION to the
backend process number. This can help identifying the
Oracle session and allows you
to trace it with DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.
oracle_fdw uses Oracle array interface to avoid unnecessary client-server round-trips. The batch size can be configured with the prefetch table option and is set to 50 by default.
Rather than using a PLAN_TABLE to explain an
Oracle query (which would require such a
table to be created in the Oracle database),
oracle_fdw uses execution plans stored in the
library cache. For that, an Oracle query is
explicitly described, which forces
Oracle to parse the query. The hard part is
to find the SQL_ID and CHILD_NUMBER
of the statement in V$SQL because the
SQL_TEXT column contains only the first 1000 bytes
of the query. Therefore, oracle_fdw adds a
comment to the query that contains a hash of the query text. This
is used to search in V$SQL. The actual execution
plan or cost information is retrieved from
V$SQL_PLAN.
oracle_fdw uses transaction isolation level
SERIALIZABLE on the Oracle
side, which corresponds to Postgres Pro's
REPEATABLE READ. This is necessary because a single
Postgres Pro statement can lead to multiple
Oracle queries (e.g. during a nested loop join)
and the results need to be consistent. Unfortunately, the
Oracle implementation of
SERIALIZABLE has certain quirks; see the
Problems section for more.
The Oracle transaction is committed immediately before the local transaction commits, so that a completed Postgres Pro transaction guarantees that the Oracle transaction has completed. However, there is a small chance that the Postgres Pro transaction cannot complete even though the Oracle transaction is committed. This cannot be avoided without using two-phase transactions and a transaction manager, which is beyond what a foreign data wrapper can reasonably provide. Prepared statements involving Oracle are not supported for the same reason.
This is an example how to use oracle_fdw. More detailed information is provided in the sections Options and Usage. You should also read the documentation on foreign data and the commands referenced there.
For the sake of this example, let's assume you can connect to
Oracle as the operating system user
postgres (or whoever starts
the Postgres Pro server) with the following command:
sqlplus orauser/orapwd@//dbserver.mydomain.com:1521/ORADB
That means that the Oracle client and the environment are set up correctly. It is also assumed that oracle_fdw has been installed (see the Installation section).
We want to access a table defined like this:
SQL> DESCRIBE oratab Name Null? Type ------------------------------- -------- ------------ ID NOT NULL NUMBER(5) TEXT VARCHAR2(30) FLOATING NOT NULL NUMBER(7,2)
Then configure oracle_fdw as a Postgres Pro superuser like this:
pgdb=# CREATE EXTENSION oracle_fdw;
pgdb=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//dbserver.mydomain.com:1521/ORADB');
You can use other naming methods or local connections, see the description of dbserver below.
It is a good idea to use a superuser only where really necessary, so let's allow a normal user to use the foreign server (this is not required for the example to work but recommended):
pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
Then you can connect to Postgres Pro as
pguser and define the following:
pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb
OPTIONS (user 'orauser', password 'orapwd');
You can use external authentication to avoid storing Oracle passwords.
pgdb=> CREATE FOREIGN TABLE oratab (
id integer OPTIONS (key 'true') NOT NULL,
text character varying(30),
floating double precision NOT NULL
) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');
Remember that the table and schema name (the latter is optional) must normally be in uppercase.
Now you can use the table like a regular Postgres Pro table.
The Oracle user will need the
CREATE SESSION privilege and the right to select
from the table or view in question. Note that
oracle_fdw accesses the
Oracle table at query planning time
to get its definition. This happens before
permissions on the foreign table are checked. Consequently, you
may receive an Oracle error if you try
to access a foreign table on which you have no permissions in
Postgres Pro. This is expected
and no security problem.
For EXPLAIN VERBOSE, the user will also need
SELECT privileges on V$SQL
and V$SQL_PLAN.
oracle_fdw caches Oracle connections because it is expensive to create an Oracle session for each individual query. All connections are automatically closed when the Postgres Pro session ends.
The close_connections
function can be used to close all cached Oracle
connections. This can be useful for long-running sessions that do not
access foreign tables all the time and want to avoid blocking the
resources needed by an open Oracle connection.
You cannot call this function inside a transaction that modifies
Oracle data.
When you define a foreign table, the columns of the Oracle table are mapped to the Postgres Pro columns in the order of their definition.
oracle_fdw will only include those columns in the Oracle query that are actually needed by the Postgres Pro query.
The Postgres Pro table can have more or less columns than the Oracle table. If it has more columns, and these columns are used, you will receive a warning and NULL values will be returned.
If you want to UPDATE or DELETE,
make sure that the key option is set on all columns
that belong to the table's primary key. Failure to do so will result
in errors.
You must define the Postgres Pro columns with
data types that oracle_fdw can translate
(see the conversion table below). This restriction is only enforced if
the column actually gets used, so you can define “dummy”
columns for untranslatable data types as long as you don't access them
(this trick only works with SELECT, not when modifying
foreign data). If an Oracle value
exceeds the size of the Postgres Pro column
(e.g., the length of a varchar column or the maximal
integer value), you will receive a runtime error.
These conversions are automatically handled by oracle_fdw:
Oracle type | Possible PostgreSQL types
-------------------------+--------------------------------------------------
CHAR | char, varchar, text
NCHAR | char, varchar, text
VARCHAR | char, varchar, text
VARCHAR2 | char, varchar, text, json
NVARCHAR2 | char, varchar, text
CLOB | char, varchar, text, json
LONG | char, varchar, text
RAW | uuid, bytea
BLOB | bytea
BFILE | bytea (read-only)
LONG RAW | bytea
NUMBER | numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0 | numeric, float4, float8, int2, int4, int8,
| boolean, char, varchar, text
FLOAT | numeric, float4, float8, char, varchar, text
BINARY_FLOAT | numeric, float4, float8, char, varchar, text
BINARY_DOUBLE | numeric, float4, float8, char, varchar, text
DATE | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH | date, timestamp, timestamptz, char, varchar, text
LOCAL TIME ZONE |
INTERVAL YEAR TO MONTH | interval, char, varchar, text
INTERVAL DAY TO SECOND | interval, char, varchar, text
XMLTYPE | xml, char, varchar, text
MDSYS.SDO_GEOMETRY | geometry (see "PostGIS support" below)
If a NUMBER is converted to a boolean, 0 means
false, everything else true.
Inserting or updating XMLTYPE only works with values that
do not exceed the maximum length of the VARCHAR2 data type
(4000 or 32767, depending on the MAX_STRING_SIZE
parameter).
NCLOB is currently not supported because
Oracle cannot automatically convert it to
the client encoding.
If you want to convert TIMESTAMP WITH LOCAL TIME ZONE to
timestamp, consider setting the
set_timezone option on the foreign server.
If you need conversions exceeding the above, define an appropriate view in Oracle or Postgres Pro.
WHERE Conditions and ORDER BY Clauses #
Postgres Pro will use all applicable parts of
the WHERE clause as a filter for the scan. The
Oracle query that
oracle_fdw constructs will contain a
WHERE clause corresponding to these filter criteria
whenever such a condition can safely be translated to
Oracle SQL. This feature, also known as
push-down of WHERE clauses,
can greatly reduce the number of rows retrieved from
Oracle and may enable
Oracle's optimizer to choose
a good plan for accessing the required tables.
Similarly, ORDER BY clauses will be pushed down to
Oracle wherever possible. Note that no
ORDER BY condition that sorts by a character string
will be pushed down as the sort orders in
Postgres Pro and Oracle
cannot be guaranteed to be the same.
To make use of that, try to use simple conditions for the foreign table. Choose Postgres Pro column data types that correspond to Oracle's types because otherwise conditions cannot be translated.
The expressions now(),
transaction_timestamp(),
current_timestamp,
current_date, and
localtimestamp will be translated correctly.
The output of EXPLAIN will show the
Oracle query used so you can see which
conditions were translated to Oracle and how.
oracle_fdw can push down joins to the Oracle server, that is, a join between two foreign tables will lead to a single Oracle query that performs the join on the Oracle side.
There are some restrictions when this can happen:
Both tables must be defined on the same foreign server.
Joins between three or more tables won't be pushed down.
The join must be in a SELECT statement.
oracle_fdw must be able to push down
all join conditions and WHERE clauses.
Cross joins without join conditions are not pushed down.
If a join is pushed down, ORDER BY clauses will
not be pushed down.
It is important that table statistics for both foreign tables have been
collected with ANALYZE for
Postgres Pro to determine the best join strategy.
oracle_fdw supports INSERT,
UPDATE, and DELETE on foreign tables.
This is allowed by default (also in databases upgraded from an earlier
Postgres Pro release) and can be disabled by
setting the readonly table option.
For UPDATE and DELETE to work,
the columns corresponding to the primary key columns of the
Oracle table must have the
key column option set. These
columns are used to identify a foreign table row, so make sure
that the option is set on all columns that
belong to the primary key.
If you omit a foreign table column during INSERT,
that column is set to the value defined in the DEFAULT
clause on the Postgres Pro
foreign table (or NULL if there is no DEFAULT clause).
DEFAULT clauses on the corresponding
Oracle columns are not used. If the
Postgres Pro foreign table does not include all
columns of the Oracle table, the
Oracle DEFAULT clauses
will be used for the columns not included in the foreign table definition.
The RETURNING clause on INSERT,
UPDATE and DELETE is supported
except for columns with Oracle
data types LONG and LONG RAW
(Oracle doesn't support these data types in
the RETURNING clause).
Triggers on foreign tables are supported.
Triggers defined with AFTER and
FOR EACH ROW require that the
foreign table has no columns with Oracle data
type LONG or LONG RAW. This is because such
triggers make use of the RETURNING clause mentioned
above.
While modifying foreign data works, the performance is not particularly good, specifically when many rows are affected, because (owing to the way foreign data wrappers work) each row has to be treated individually.
Transactions are forwarded to Oracle so
BEGIN, COMMIT,
ROLLBACK, and SAVEPOINT work as
expected. Prepared statements involving Oracle
are not supported. See the
Internals section for details.
Since oracle_fdw uses serialized transactions by default, it is possible that data modifying statements lead to a serialization failure:
ORA-08177: can't serialize access for this transaction
This can happen if concurrent transactions modify the table and
gets more likely in long running transactions. Such errors can be
identified by their SQLSTATE (40001). An application
using oracle_fdw should retry transactions
that fail with this error.
It is possible to use a different transaction isolation level, see Foreign Server Options for a discussion.
Postgres Pro's EXPLAIN
will show the query that is actually issued to
Oracle. EXPLAIN VERBOSE
will show Oracle's execution plan (that
will not work with Oracle server 9i or older,
see Problems).
You can use ANALYZE to gather statistics on a foreign table. This is supported by oracle_fdw.
Without statistics, Postgres Pro has no way to estimate the row count for queries on a foreign table, which can cause bad execution plans to be chosen.
Postgres Pro will not
automatically gather statistics for foreign tables with the autovacuum
daemon like it does for normal tables, so it is particularly important
to run ANALYZE on foreign tables after creation and
whenever the remote table has changed significantly.
Keep in mind that analyzing an Oracle foreign table will result in a full sequential table scan. You can use the table option sample_percent to speed this up by using only a sample of the Oracle table.
The data type geometry is only available when
PostGIS is installed.
The only supported geometry types are POINT,
LINE, POLYGON, MULTIPOINT,
MULTILINE, and MULTIPOLYGON in two and three
dimensions. Empty PostGIS geometries are not
supported because they have no equivalent in
Oracle Spatial.
NULL values for Oracle SRID
will be converted to 0 and vice versa. For other conversions between
Oracle SRID and
PostGIS SRID, create a
file srid.map in the
Postgres Pro share directory.
Each line of this file shall contain an Oracle
SRID and the corresponding PostGIS
SRID, separated by a whitespace. Keep the file small
for good performance.
IMPORT FOREIGN SCHEMA #
IMPORT FOREIGN SCHEMA is supported to bulk import
table definitions for all tables in an Oracle
schema. In addition to the documentation of IMPORT FOREIGN
SCHEMA, consider the following:
IMPORT FOREIGN SCHEMA will create foreign tables
for all objects found in ALL_TAB_COLUMNS.
That includes tables, views and materialized views, but not synonyms.
These are the supported options for IMPORT FOREIGN SCHEMA:
case controls case folding for table and
column names during import.
The possible values are:
keep: leave the names as they are
in Oracle, usually in upper case.
lower: translate all table and column
names to lower case.
smart: only translate names that
are all upper case in Oracle
(this is the default).
collation is the collation used for case
folding for the lower and
smart options of case.
The default value is default, which is
the database's default collation. Only collations in the
pg_catalog schema are supported. See
the collname values in the
pg_collation catalog for a list of
possible values.
dblink is the Oracle
database link through which the schema is accessed.
This name must be written exactly as it occurs in Oracle's system catalog so normally consist of uppercase letters only.
readonly sets the
readonly option on all
imported tables.
skip_tables (default
false): don't import tables.
skip_views (default
false): don't import views.
skip_matviews
(default false): don't import
materialized views.
max_long sets the
max_long option on all
imported tables.
sample_percent sets
the sample_percent
option on all imported tables.
prefetch sets the
prefetch option on all
imported tables.
lob_prefetch sets
the lob_prefetch
option on all imported tables.
nchar sets the
nchar option on all
imported tables.
set_timezone sets
the set_timezone
option on all imported tables.
The Oracle schema name must be written
exactly as it is in Oracle, so normally
in upper case. Since Postgres Pro
translates names to lower case before processing, you must protect
the schema name with double quotes (for example,
"SCOTT").
Table names in the LIMIT TO or
EXCEPT clause must be written as they will appear
in Postgres Pro after the case folding
described above.
Note that IMPORT FOREIGN SCHEMA does not work with
Oracle server 8i; see the
Problems section for details.
oracle_fdw_handler() RETURNS fdw_handler
oracle_fdw_validator(text[], oid) RETURNS void
#These functions are the handler and validator functions necessary to create a foreign data wrapper.
FOREIGN DATA WRAPPER oracle_fdw HANDLER oracle_fdw_handler VALIDATOR oracle_fdw_validator
The extension automatically creates a foreign data wrapper named
oracle_fdw. Normally, that's all you need, and you
can proceed to define foreign servers. You can create additional
Oracle foreign data wrappers, for example,
if you need to set the nls_lang option
(you can alter the existing oracle_fdw wrapper,
but all modifications will be lost after a dump/restore).
oracle_close_connections() RETURNS void
#This function can be used to close all open Oracle connections in this session. See Usage for further description.
oracle_diag(name DEFAULT NULL) RETURNS text
#This function is useful for diagnostic purposes only. It will return the versions of oracle_fdw, Postgres Pro server, and Oracle client. If called with no argument or NULL, it will additionally return the values of some environment variables used for establishing Oracle connections. If called with the name of a foreign server, it will additionally return the Oracle server version.
oracle_execute(server name, stmt text) RETURNS void
#This function can be used to execute arbitrary SQL statements on the remote Oracle server. That will only work with statements that do not return results (typically DDL statements).
Be careful when using this function since it might disturb the
transaction management of oracle_fdw.
Remember that running a DDL statement in Oracle
will issue an implicit COMMIT. You are best advised
to use this function outside of multi-statement transactions.
If you modify the default foreign data wrapper
oracle_fdw, any changes will be lost upon
dump/restore. Create a new foreign data wrapper if you want the
options to be persistent. The SQL script shipped with the software
contains a CREATE FOREIGN DATA WRAPPER statement
you can use.
[nls_lang] #
Sets the NLS_LANG environment variable for
Oracle to this value.
NLS_LANG is in the form
language_territory.charset
(for example, AMERICAN_AMERICA.AL32UTF8).
This must match your database encoding. When this value is not set,
oracle_fdw will automatically do the
right thing if it can and issue a warning if it cannot. Set this
only if you know what you are doing.
See the Problems section.
dbserver #
The Oracle database connection string
for the remote database. This can be in any of the forms that
Oracle supports as long as your
Oracle client is configured accordingly.
Set this to an empty string for local (BEQUEATH)
connections.
[isolation_level] #
The transaction isolation level to use at the
Oracle database. The value can be
serializable, read_committed,
or read_only. The default is
serializable.
Note that the Oracle table can be
queried more than once during a single
Postgres Pro statement (for example,
during a nested loop join). To make sure that no inconsistencies
caused by race conditions with concurrent transactions can occur,
the transaction isolation level must guarantee read stability.
This is only guaranteed with Oracle's
SERIALIZABLE or READ ONLY
isolation levels.
Unfortunately Oracle's implementation of
SERIALIZABLE is rather bad and causes
serialization errors (ORA-08177) in unexpected situations, like
inserts into the table. Using READ COMMITTED
transactions works around this problem, but there is a
risk of inconsistencies. If you want to use it, check
your execution plans if the foreign scan could be executed more
than once.
[nchar] #
Setting this option to on chooses a more
expensive character conversion on the
Oracle side. This is required if
Oracle tables have NCHAR
or NVARCHAR2 columns that contain characters that
cannot be represented in the Oracle
database character set. The default is off.
Setting nchar to on
has a noticable performance impact, and it causes ORA-01461
errors with UPDATE statements that set strings
over 2000 bytes (or 16383 if you have MAX_STRING_SIZE
= EXTENDED). This error seems to be an
Oracle bug.
[set_timezone] #
Setting this option to on sets the
Oracle session time zone to the
current value of the Postgres Pro
parameter timezone when the connection to
Oracle is made. This is only useful
if you plan to use Oracle columns of
type TIMESTAMP WITH LOCAL TIME ZONE and
want to translate them to timestamp without time zone
in Postgres Pro. The default is
off.
Note that if you change timezone after
the Oracle connection has been
established, oracle_fdw will not
change the Oracle session time zone.
You can call
oracle_close_connections() RETURNS void
in that case so that a new connection is opened the next time
you access a foreign table.
If Oracle does not recognize the time zone, connections will fail with an error like “ORA-01882: timezone region not found”.
In that case, either use a different timezone
or leave the option set to off and set the
environment variable ORA_SDTZ to an appropriate
value in the environment of the
Postgres Pro server.
table #The Oracle table name. This name must be written exactly as it occurs in Oracle's system catalog so normally consist of uppercase letters only.
To define a foreign table based on an arbitrary Oracle query, set this option to the query enclosed in parentheses:
OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')
Do not set the schema option
in this case. INSERT, UPDATE,
and DELETE will work on foreign tables
defined on simple queries; if you want to avoid that (or
confusing Oracle error messages
for more complicated queries), use the table option
readonly.
dblink #The Oracle database link through which the table is accessed. This name must be written exactly as it occurs in Oracle's system catalog so normally consist of uppercase letters only.
[schema] #The table's schema (or owner). Useful to access tables that do not belong to the connecting Oracle user. This name must be written exactly as it occurs in Oracle's system catalog so normally consist of uppercase letters only.
[max_long] #
The maximal length of any LONG, LONG RAW,
and XMLTYPE columns in the
Oracle table. Possible values are
integers between 1 and 1073741823 (the maximal size of a
bytea in Postgres Pro).
This amount of memory will be allocated at least twice so large
values will consume a lot of memory. If
max_long is less than the length
of the longest value retrieved, you will receive the error message
“ORA-01406: fetched column value was truncated”.
The default is 32767.
[readonly] #
INSERT, UPDATE, and
DELETE are only allowed on tables where this
option is not set to yes/on/true. The default is
false.
[sample_percent] #
This option only influences ANALYZE processing
and can be useful to ANALYZE very large tables
in a reasonable time.
The value must be between 0.000001 and 100 and defines the
percentage of Oracle table blocks
that will be randomly selected to calculate
Postgres Pro table statistics. This is
accomplished using the SAMPLE BLOCK (x)
clause in Oracle.
The default is 100.
ANALYZE will fail with ORA-00933 for tables
defined with Oracle queries and may
fail with ORA-01446 for tables defined with complex
Oracle views.
[prefetch] #
Sets the number of rows that will be fetched with a single
round-trip between Postgres Pro and
Oracle during a foreign table scan.
The value must be between 1 and 1000, where a value of zero
disables prefetching. The default is 50.
Higher values can speed up performance but will use more memory on the Postgres Pro server.
Note that there is no prefetching if the
Oracle table contains columns of
the type MDSYS.SDO_GEOMETRY.
[lob_prefetch] #
Sets the number of bytes that are prefetched for BLOB,
CLOB, and BFILE values. LOBs that
exceed that size will require additional round trips between
Postgres Pro and
Oracle so setting this value bigger
than the size of your typical LOB will be good for performance.
Choosing bigger values for this option can allocate more memory
on the server side but will boost performance for large LOBs.
The default is 1048576.
[key] #
If set to yes/on/true, the corresponding column on the
foreign Oracle table is considered
a primary key column. For UPDATE and
DELETE to work, you must set this option on
all columns that belong to the table's primary key.
The default is false.
[strip_zeros] #
If set to yes/on/true, ASCII 0 characters will be removed from
the string during transfer. Such characters are valid in
Oracle but not in
Postgres Pro so they will cause an error
when read by oracle_fdw. This option
only makes sense for character,
character varying, and text columns.
The default is false.
Characters stored in an Oracle database that cannot be converted to the Postgres Pro database encoding will silently be replaced by replacement characters, typically a normal or inverted question mark, by Oracle. You will get no warning or error messages.
If you use a Postgres Pro database encoding
that Oracle does not know (currently these
are EUC_CN, EUC_KR,
LATIN10, MULE_INTERNAL,
WIN874, and SQL_ASCII),
non-ASCII characters cannot be translated correctly. You will get
a warning in this case, and the characters will be replaced by
replacement characters as described above.
You can set the nls_lang option
of the foreign data wrapper to force a certain
Oracle encoding, but the resulting characters
will most likely be incorrect and lead to
Postgres Pro error messages. This is probably
only useful for SQL_ASCII encoding if you know
what you are doing.
The definition of the Oracle system catalogs
V$SQL and V$SQL_PLAN
has changed with Oracle 10.1. Using
EXPLAIN VERBOSE with older
Oracle server versions will result in
errors like:
ERROR: error describing query: OCIStmtExecute failed to execute
remote query for sql_id
DETAIL: ORA-00904: "LAST_ACTIVE_TIME": invalid identifier
There is no plan to fix this, since Oracle 9i has been out of Extended Support since 2010 and the functionality is not essential.
IMPORT FOREIGN SCHEMA throws the following error
with Oracle server 8i:
ERROR: error importing foreign schema: OCIStmtExecute failed to execute
column query
DETAIL: ORA-00904: invalid column name
This is because the view ALL_TAB_COLUMNS
lacks the column CHAR_LENGTH, which was
added in Oracle 9i.
The Oracle client shared library comes
with its own LDAP client implementation conforming to
RFC 1823
so these functions have the same names as
OpenLDAP's. This will lead to a name
collision when the Postgres Pro server was
configured --with-ldap.
The name collision will not be detected because oracle_fdw is loaded at runtime, but trouble will happen if anybody calls an LDAP function. Typically, OpenLDAP is loaded first so if Oracle calls an LDAP function (for example, if you use directory naming name resolution), the backend will crash. This can lead to messages like the following (seen on Linux) in the Postgres Pro server log:
../../../libraries/libldap/getentry.c:29: ldap_first_entry: Assertion `( (ld)->ld_options.ldo_valid == 0x2 )' failed.
Since Postgres Pro is built
--with-ldap, it may work as long as you don't use
any LDAP client functionality in Oracle.
On some platforms, you can force Oracle's
client shared library to be loaded before the
Postgres Pro server is started
(LD_PRELOAD on Linux). Then
Oracle's LDAP functions should get used.
In that case, Oracle may be able to use LDAP
functionality, but using LDAP from Postgres Pro
will crash the backend.
You cannot use LDAP functionality both in Postgres Pro and in Oracle.
In Oracle 11.2 or above, inserting the first row into a newly created Oracle table with oracle_fdw will lead to a serialization error.
This is because of an Oracle feature called deferred segment creation, which defers allocation of storage space for a new table until the first row is inserted. This causes a serialization failure with serializable transactions.
This is no serious problem; you can work around it by either
ignoring that first error or creating the table with
SEGMENT CREATION IMMEDIATE.
A much nastier problem is that concurrent inserts can sometimes cause serialization errors when an index page is split concurrently with a modifying serializable transaction.
Oracle claims that this is not a bug, and the suggested solution is to retry the transaction that got a serialization error.
This is the list of Oracle bugs that affect or have affected oracle_fdw in the past.
Bug 2728408 can cause “ORA-8177 cannot serialize access for this transaction” even if no modification of remote data is attempted. It can occur with Oracle server 8.1.7.4 (install one-off patch 2728408) or Oracle server 9.2 (install Patch Set 9.2.0.4 or better).
Oracle client 21c is known not to work for
CLOB columns (they appear empty). There is no ultimate
proof that that is an Oracle bug, but other
versions are working fine.
Laurenz Albe, with notable contributions from Vincent Mora of Oslandia and Tatsuro Yamada of the NTT OSS Center.