| H.1. oracle_fdw — access toOracle databases | ||||
|---|---|---|---|---|
| Prev | Up | Appendix H. Third-Party Modules and Extensions Shipped as Individual Packages | Home | Next |
H.1. oracle_fdw — access toOracle databases#
Theoracle_fdw module is aPostgres Pro extension that provides a Foreign Data Wrapper for easy and efficient access toOracle databases, including pushdown ofWHERE conditions and required columns as well as comprehensiveEXPLAIN support.
H.1.1. Installation#
Theoracle_fdw extension is provided withPostgres Pro Standard as a separate pre-built packageoracle-fdw-std-17 (for the detailed installation instructions, seeChapter 16).oracle_fdw requiresOracle Instant Client version 19.5.
For RPM-based systems (RHEL, SUSE, Red OS, ROSA, ALT Linux), download theoracle-instantclient RPM and install it usingrpm. If you use a Debian-based system (Ubuntu, Astra Linux), you have to either convert the RPM package to a Debian package usingalien, and then install it usingdpkg, or download the ZIP archive of the client and extract the contents of the archive into the/opt/oracle directory.
Once you havePostgres Pro Standard installed, create theoracle_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 thepsql\dx command or the system catalogpg_available_extensions isnot the installed version oforacle_fdw. To get theoracle_fdw version, use theoracle_diag function.
H.1.2. Internals#
oracle_fdw sets theMODULE of theOracle session topostgres and theACTION to the backend process number. This can help identifying theOracle session and allows you to trace it withDBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.
oracle_fdw usesOracle array interface to avoid unnecessary client-server round-trips. The batch size can be configured with theprefetch table option and is set to 50 by default.
Rather than using aPLAN_TABLE to explain anOracle query (which would require such a table to be created in theOracle database),oracle_fdw uses execution plans stored in the library cache. For that, anOracle query isexplicitly described, which forcesOracle to parse the query. The hard part is to find theSQL_ID andCHILD_NUMBER of the statement inV$SQL because theSQL_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 inV$SQL. The actual execution plan or cost information is retrieved fromV$SQL_PLAN.
oracle_fdw uses transaction isolation levelSERIALIZABLE on theOracle side, which corresponds toPostgres Pro'sREPEATABLE READ. This is necessary because a singlePostgres Pro statement can lead to multipleOracle queries (e.g. during a nested loop join) and the results need to be consistent. Unfortunately, theOracle implementation ofSERIALIZABLE has certain quirks; see theProblems section for more.
TheOracle transaction is committed immediately before the local transaction commits, so that a completedPostgres Pro transaction guarantees that theOracle transaction has completed. However, there is a small chance that thePostgres Pro transaction cannot complete even though theOracle 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 involvingOracle are not supported for the same reason.
H.1.3. Short Simple Example#
This is an example how to useoracle_fdw. More detailed information is provided in the sectionsOptions andUsage. You should also read thedocumentation on foreign data and the commands referenced there.
For the sake of this example, let's assume you can connect toOracle as the operating system userpostgres (or whoever starts thePostgres Pro server) with the following command:
sqlplus orauser/orapwd@//dbserver.mydomain.com:1521/ORADB
That means that theOracle client and the environment are set up correctly. It is also assumed thatoracle_fdw has been installed (see theInstallation 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 configureoracle_fdw as aPostgres 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 ofdbserver 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 toPostgres Pro aspguser and define the following:
pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb OPTIONS (user 'orauser', password 'orapwd');
You can use external authentication to avoid storingOracle 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 regularPostgres Pro table.
H.1.4. Usage#
H.1.4.1. Oracle Permissions#
TheOracle user will need theCREATE SESSION privilege and the right to select from the table or view in question. Note thatoracle_fdw accesses theOracle table at query planning time to get its definition. This happensbefore permissions on the foreign table are checked. Consequently, you may receive anOracle error if you try to access a foreign table on which you have no permissions inPostgres Pro. This is expected and no security problem.
ForEXPLAIN VERBOSE, the user will also needSELECT privileges onV$SQL andV$SQL_PLAN.
H.1.4.2. Connections#
oracle_fdw cachesOracle connections because it is expensive to create anOracle session for each individual query. All connections are automatically closed when thePostgres Pro session ends.
Theclose_connections function can be used to close all cachedOracle 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 openOracle connection. You cannot call this function inside a transaction that modifiesOracle data.
H.1.4.3. Columns#
When you define a foreign table, the columns of theOracle table are mapped to thePostgres Pro columns in the order of their definition.
oracle_fdw will only include those columns in theOracle query that are actually needed by thePostgres Pro query.
ThePostgres Pro table can have more or less columns than theOracle 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 toUPDATE orDELETE, make sure that thekey option is set on all columns that belong to the table's primary key. Failure to do so will result in errors.
H.1.4.4. Data Types#
You must define thePostgres Pro columns with data types thatoracle_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 withSELECT, not when modifying foreign data). If anOracle value exceeds the size of thePostgres Pro column (e.g., the length of avarchar column or the maximalinteger value), you will receive a runtime error.
These conversions are automatically handled byoracle_fdw:
Oracle type | Possible PostgreSQL types-------------------------+--------------------------------------------------CHAR | char, varchar, textNCHAR | char, varchar, textVARCHAR | char, varchar, textVARCHAR2 | char, varchar, text, jsonNVARCHAR2 | char, varchar, textCLOB | char, varchar, text, jsonLONG | char, varchar, textRAW | uuid, byteaBLOB | byteaBFILE | bytea (read-only)LONG RAW | byteaNUMBER | numeric, float4, float8, char, varchar, textNUMBER(n,m) with m<=0 | numeric, float4, float8, int2, int4, int8, | boolean, char, varchar, textFLOAT | numeric, float4, float8, char, varchar, textBINARY_FLOAT | numeric, float4, float8, char, varchar, textBINARY_DOUBLE | numeric, float4, float8, char, varchar, textDATE | date, timestamp, timestamptz, char, varchar, textTIMESTAMP | date, timestamp, timestamptz, char, varchar, textTIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, textTIMESTAMP WITH | date, timestamp, timestamptz, char, varchar, text LOCAL TIME ZONE |INTERVAL YEAR TO MONTH | interval, char, varchar, textINTERVAL DAY TO SECOND | interval, char, varchar, textXMLTYPE | xml, char, varchar, textMDSYS.SDO_GEOMETRY | geometry (see "PostGIS support" below)
If aNUMBER is converted to aboolean, 0 meansfalse, everything elsetrue.
Inserting or updatingXMLTYPE only works with values that do not exceed the maximum length of theVARCHAR2 data type (4000 or 32767, depending on theMAX_STRING_SIZE parameter).
NCLOB is currently not supported becauseOracle cannot automatically convert it to the client encoding.
If you want to convertTIMESTAMP WITH LOCAL TIME ZONE totimestamp, consider setting theset_timezone option on the foreign server.
If you need conversions exceeding the above, define an appropriate view inOracle orPostgres Pro.
H.1.4.5. WHERE Conditions andORDER BY Clauses#
Postgres Pro will use all applicable parts of theWHERE clause as a filter for the scan. TheOracle query thatoracle_fdw constructs will contain aWHERE clause corresponding to these filter criteria whenever such a condition can safely be translated toOracle SQL. This feature, also known aspush-down ofWHERE clauses, can greatly reduce the number of rows retrieved fromOracle and may enableOracle's optimizer to choose a good plan for accessing the required tables.
Similarly,ORDER BY clauses will be pushed down toOracle wherever possible. Note that noORDER BY condition that sorts by a character string will be pushed down as the sort orders inPostgres Pro andOracle cannot be guaranteed to be the same.
To make use of that, try to use simple conditions for the foreign table. ChoosePostgres Pro column data types that correspond toOracle's types because otherwise conditions cannot be translated.
The expressionsnow(),transaction_timestamp(),current_timestamp,current_date, andlocaltimestamp will be translated correctly.
The output ofEXPLAIN will show theOracle query used so you can see which conditions were translated toOracle and how.
H.1.4.6. Joins Between Foreign Tables#
oracle_fdw can push down joins to theOracle server, that is, a join between two foreign tables will lead to a singleOracle query that performs the join on theOracle 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
SELECTstatement.oracle_fdw must be able to push down all join conditions and
WHEREclauses.Cross joins without join conditions are not pushed down.
If a join is pushed down,
ORDER BYclauses will not be pushed down.
It is important that table statistics for both foreign tables have been collected withANALYZE forPostgres Pro to determine the best join strategy.
H.1.4.7. Modifying Foreign Data#
oracle_fdw supportsINSERT,UPDATE, andDELETE on foreign tables. This is allowed by default (also in databases upgraded from an earlierPostgres Pro release) and can be disabled by setting thereadonly table option.
ForUPDATE andDELETE to work, the columns corresponding to the primary key columns of theOracle table must have thekey column option set. These columns are used to identify a foreign table row, so make sure that the option is set onall columns that belong to the primary key.
If you omit a foreign table column duringINSERT, that column is set to the value defined in theDEFAULT clause on thePostgres Pro foreign table (or NULL if there is noDEFAULT clause).DEFAULT clauses on the correspondingOracle columns are not used. If thePostgres Pro foreign table does not include all columns of theOracle table, theOracleDEFAULT clauses will be used for the columns not included in the foreign table definition.
TheRETURNING clause onINSERT,UPDATE andDELETE is supported except for columns withOracle data typesLONG andLONG RAW (Oracle doesn't support these data types in theRETURNING clause).
Triggers on foreign tables are supported. Triggers defined withAFTER andFOR EACH ROW require that the foreign table has no columns withOracle data typeLONG orLONG RAW. This is because such triggers make use of theRETURNING 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 toOracle soBEGIN,COMMIT,ROLLBACK, andSAVEPOINT work as expected. Prepared statements involvingOracle are not supported. See theInternals section for details.
Sinceoracle_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 theirSQLSTATE (40001). An application usingoracle_fdw should retry transactions that fail with this error.
It is possible to use a different transaction isolation level, seeForeign Server Options for a discussion.
H.1.4.8. EXPLAIN#
Postgres Pro'sEXPLAIN will show the query that is actually issued toOracle.EXPLAIN VERBOSE will showOracle's execution plan (that will not work withOracle server 9i or older, seeProblems).
H.1.4.9. ANALYZE#
You can useANALYZE to gather statistics on a foreign table. This is supported byoracle_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 willnot automatically gather statistics for foreign tables with the autovacuum daemon like it does for normal tables, so it is particularly important to runANALYZE on foreign tables after creation and whenever the remote table has changed significantly.
Keep in mind that analyzing anOracle foreign table will result in a full sequential table scan. You can use the table optionsample_percent to speed this up by using only a sample of theOracle table.
H.1.4.10. PostGIS Support#
The data typegeometry is only available whenPostGIS is installed.
The only supported geometry types arePOINT,LINE,POLYGON,MULTIPOINT,MULTILINE, andMULTIPOLYGON in two and three dimensions. EmptyPostGIS geometries are not supported because they have no equivalent inOracle Spatial.
NULL values forOracleSRID will be converted to 0 and vice versa. For other conversions betweenOracleSRID andPostGISSRID, create a filesrid.map in thePostgres Proshare directory. Each line of this file shall contain anOracleSRID and the correspondingPostGISSRID, separated by a whitespace. Keep the file small for good performance.
H.1.4.11. Support forIMPORT FOREIGN SCHEMA#
IMPORT FOREIGN SCHEMA is supported to bulk import table definitions for all tables in anOracle schema. In addition to the documentation ofIMPORT FOREIGN SCHEMA, consider the following:
IMPORT FOREIGN SCHEMAwill create foreign tables for all objects found inALL_TAB_COLUMNS. That includes tables, views and materialized views, but not synonyms.These are the supported options for
IMPORT FOREIGN SCHEMA:casecontrols case folding for table and column names during import.The possible values are:
keep: leave the names as they are inOracle, usually in upper case.lower: translate all table and column names to lower case.smart: only translate names that are all upper case inOracle (this is the default).
collationis the collation used for case folding for thelowerandsmartoptions ofcase.The default value is
default, which is the database's default collation. Only collations in thepg_catalogschema are supported. See thecollnamevalues in thepg_collationcatalog for a list of possible values.dblinkis theOracle database link through which the schema is accessed.This name must be written exactly as it occurs inOracle's system catalog so normally consist of uppercase letters only.
readonlysets thereadonly option on all imported tables.skip_tables(defaultfalse): don't import tables.skip_views(defaultfalse): don't import views.skip_matviews(defaultfalse): don't import materialized views.max_longsets themax_long option on all imported tables.sample_percentsets thesample_percent option on all imported tables.prefetchsets theprefetch option on all imported tables.lob_prefetchsets thelob_prefetch option on all imported tables.ncharsets thenchar option on all imported tables.set_timezonesets theset_timezone option on all imported tables.
TheOracle schema name must be written exactly as it is inOracle, so normally in upper case. SincePostgres 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 TOorEXCEPTclause must be written as they will appear inPostgres Pro after the case folding described above.
Note thatIMPORT FOREIGN SCHEMA does not work withOracle server 8i; see theProblems section for details.
H.1.5. Reference#
H.1.5.1. Objects Created by the Extension#
oracle_fdw_handler() RETURNS fdw_handleroracle_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 additionalOracle foreign data wrappers, for example, if you need to set thenls_lang option (you can alter the existingoracle_fdwwrapper, but all modifications will be lost after a dump/restore).oracle_close_connections() RETURNS void#This function can be used to close all openOracle connections in this session. SeeUsage for further description.
oracle_diag(name DEFAULT NULL) RETURNS text#This function is useful for diagnostic purposes only. It will return the versions oforacle_fdw,Postgres Pro server, andOracle client. If called with no argument or NULL, it will additionally return the values of some environment variables used for establishingOracle connections. If called with the name of a foreign server, it will additionally return theOracle server version.
oracle_execute(server name, stmt text) RETURNS void#This function can be used to execute arbitrary SQL statements on the remoteOracle 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 oforacle_fdw. Remember that running a DDL statement inOracle will issue an implicit
COMMIT. You are best advised to use this function outside of multi-statement transactions.
H.1.5.2. Options#
H.1.5.2.1. Foreign Data Wrapper Options#
Important
If you modify the default foreign data wrapperoracle_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 aCREATE FOREIGN DATA WRAPPER statement you can use.
[nls_lang]#Sets the
NLS_LANGenvironment variable forOracle to this value.NLS_LANGis in the formlanguage_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 theProblems section.
H.1.5.2.2. Foreign Server Options#
dbserver#TheOracle database connection string for the remote database. This can be in any of the forms thatOracle supports as long as yourOracle client is configured accordingly. Set this to an empty string for local (
BEQUEATH) connections.[isolation_level]#The transaction isolation level to use at theOracle database. The value can be
serializable,read_committed, orread_only. The default isserializable.Note that theOracle table can be queried more than once during a singlePostgres 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 withOracle's
SERIALIZABLEorREAD ONLYisolation levels.UnfortunatelyOracle's implementation of
SERIALIZABLEis rather bad and causes serialization errors (ORA-08177) in unexpected situations, like inserts into the table. UsingREAD COMMITTEDtransactions works around this problem, butthere 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
onchooses a more expensive character conversion on theOracle side. This is required ifOracle tables haveNCHARorNVARCHAR2columns that contain characters that cannot be represented in theOracle database character set. The default isoff.Setting
nchartoonhas a noticable performance impact, and it causes ORA-01461 errors withUPDATEstatements that set strings over 2000 bytes (or 16383 if you haveMAX_STRING_SIZE = EXTENDED). This error seems to be anOracle bug.[set_timezone]#Setting this option to
onsets theOracle session time zone to the current value of thePostgres Pro parametertimezonewhen the connection toOracle is made. This is only useful if you plan to useOracle columns of typeTIMESTAMP WITH LOCAL TIME ZONEand want to translate them totimestamp without time zoneinPostgres Pro. The default isoff.Note that if you change
timezoneafter theOracle connection has been established,oracle_fdw will not change theOracle session time zone. You can calloracle_close_connections() RETURNS voidin that case so that a new connection is opened the next time you access a foreign table.IfOracle 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
timezoneor leave the option set tooffand set the environment variableORA_SDTZto an appropriate value in the environment of thePostgres Pro server.
H.1.5.2.3. User Mapping Options#
H.1.5.2.4. Foreign Table Options#
table#TheOracle table name. This name must be written exactly as it occurs inOracle's system catalog so normally consist of uppercase letters only.
To define a foreign table based on an arbitraryOracle query, set this option to the query enclosed in parentheses:
OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')
Do not set theschema option in this case.
INSERT,UPDATE, andDELETEwill work on foreign tables defined on simple queries; if you want to avoid that (or confusingOracle error messages for more complicated queries), use the table optionreadonly.dblink#TheOracle database link through which the table is accessed. This name must be written exactly as it occurs inOracle'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 connectingOracle user. This name must be written exactly as it occurs inOracle's system catalog so normally consist of uppercase letters only.
[max_long]#The maximal length of any
LONG,LONG RAW, andXMLTYPEcolumns in theOracle table. Possible values are integers between 1 and 1073741823 (the maximal size of abyteainPostgres Pro). This amount of memory will be allocated at least twice so large values will consume a lot of memory. Ifmax_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 is32767.[readonly]#INSERT,UPDATE, andDELETEare only allowed on tables where this option is not set to yes/on/true. The default isfalse.[sample_percent]#This option only influences
ANALYZEprocessing and can be useful toANALYZEvery large tables in a reasonable time.The value must be between 0.000001 and 100 and defines the percentage ofOracle table blocks that will be randomly selected to calculatePostgres Pro table statistics. This is accomplished using the
SAMPLE BLOCK (x)clause inOracle. The default is100.ANALYZEwill fail with ORA-00933 for tables defined withOracle queries and may fail with ORA-01446 for tables defined with complexOracle views.[prefetch]#Sets the number of rows that will be fetched with a single round-trip betweenPostgres Pro andOracle 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 thePostgres Pro server.
Note that there is no prefetching if theOracle table contains columns of the type
MDSYS.SDO_GEOMETRY.[lob_prefetch]#Sets the number of bytes that are prefetched for
BLOB,CLOB, andBFILEvalues. LOBs that exceed that size will require additional round trips betweenPostgres Pro andOracle 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 is1048576.
H.1.5.2.5. Column Options#
[key]#If set to yes/on/true, the corresponding column on the foreignOracle table is considered a primary key column. For
UPDATEandDELETEto work, you must set this option on all columns that belong to the table's primary key. The default isfalse.[strip_zeros]#If set to yes/on/true, ASCII 0 characters will be removed from the string during transfer. Such characters are valid inOracle but not inPostgres Pro so they will cause an error when read byoracle_fdw. This option only makes sense for
character,character varying, andtextcolumns. The default isfalse.
H.1.6. Problems#
H.1.6.1. Encoding#
Characters stored in anOracle database that cannot be converted to thePostgres Pro database encoding will silently be replaced by replacement characters, typically a normal or inverted question mark, byOracle. You will get no warning or error messages.
If you use aPostgres Pro database encoding thatOracle does not know (currently these areEUC_CN,EUC_KR,LATIN10,MULE_INTERNAL,WIN874, andSQL_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 thenls_lang option of the foreign data wrapper to force a certainOracle encoding, but the resulting characters will most likely be incorrect and lead toPostgres Pro error messages. This is probably only useful forSQL_ASCII encoding if you know what you are doing.
H.1.6.2. Limited Functionality in OldOracle Versions#
The definition of theOracle system catalogsV$SQL andV$SQL_PLAN has changed withOracle 10.1. UsingEXPLAIN VERBOSE with olderOracle server versions will result in errors like:
ERROR: error describing query: OCIStmtExecute failed to execute remote query for sql_idDETAIL: ORA-00904: "LAST_ACTIVE_TIME": invalid identifier
There is no plan to fix this, sinceOracle 9i has been out of Extended Support since 2010 and the functionality is not essential.
IMPORT FOREIGN SCHEMA throws the following error withOracle server 8i:
ERROR: error importing foreign schema: OCIStmtExecute failed to execute column queryDETAIL: ORA-00904: invalid column name
This is because the viewALL_TAB_COLUMNS lacks the columnCHAR_LENGTH, which was added inOracle 9i.
H.1.6.3. LDAP Libraries#
TheOracle client shared library comes with its own LDAP client implementation conforming toRFC 1823 so these functions have the same names asOpenLDAP's. This will lead to a name collision when thePostgres Pro server was configured--with-ldap.
The name collision will not be detected becauseoracle_fdw is loaded at runtime, but trouble will happen if anybody calls an LDAP function. Typically,OpenLDAP is loaded first so ifOracle calls an LDAP function (for example, if you usedirectory naming name resolution), the backend will crash. This can lead to messages like the following (seen on Linux) in thePostgres Pro server log:
../../../libraries/libldap/getentry.c:29: ldap_first_entry: Assertion `( (ld)->ld_options.ldo_valid == 0x2 )' failed.
SincePostgres Pro is built--with-ldap, it may work as long as you don't use any LDAP client functionality inOracle. On some platforms, you can forceOracle's client shared library to be loaded before thePostgres Pro server is started (LD_PRELOAD on Linux). ThenOracle's LDAP functions should get used. In that case,Oracle may be able to use LDAP functionality, but using LDAP fromPostgres Pro will crash the backend.
You cannot use LDAP functionality both inPostgres Pro and inOracle.
H.1.6.4. Serialization Errors#
InOracle 11.2 or above, inserting the first row into a newly createdOracle table withoracle_fdw will lead to a serialization error.
This is because of anOracle feature calleddeferred 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 withSEGMENT 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.
H.1.6.5. Oracle Bugs#
This is the list ofOracle bugs that affect or have affectedoracle_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 withOracle server 8.1.7.4 (install one-off patch 2728408) orOracle server 9.2 (install Patch Set 9.2.0.4 or better).
Oracle client 21c is known not to work forCLOB columns (they appear empty). There is no ultimate proof that that is anOracle bug, but other versions are working fine.
H.1.7. Authors#
Laurenz Albe, with notable contributions from Vincent Mora of Oslandia and Tatsuro Yamada of the NTT OSS Center.