Release 2.7.0

Note: Building and running oracle_fdw with old minor releases may fail; see the installation requirement documentation for details.

Bugfixes

• Don't push down LIMIT without an ORDER BY clause.
  This can lead to wrong results in some cases.
  Report by @fbattke.
• Fix support for query parameters of type uuid.
  This led to errors like

  error executing query: OCINumberFromText failed to convert parameter
  OCI-22062: invalid input string [8feee298-7ce5-4761-9487-34cab831fead]
  

  Report by @VictorRas.
• Fix a memory leak in statements that return LOBs.
  The memory for LOB locators didn't get released before the end of the transaction, so running many statements in a single transaction could cause out-of-memory errors and server crashes.
  Report by @JosefMachytkaNetApp.
• Fix building on Windows with PostgreSQL versions older than v15.
  This used to report "unresolved external symbol XactReadOnly".
  Report by @w0pr.
• Fix crash or empty result when reading larger CLOBs.
  Report by @sabineWalter and @vlisterik.
• Bind empty string parameters as NULL values.
  Failure to do that led to the error

  ORA-01459: invalid length for variable character string
  

  during the attempt to INSERT empty strings in a foreign table.
  Report by @adrianboangiu and @intgr.

Enhancements

• Speed up IMPORT FOREIGN SCHEMA ... LIMIT TO (...) by filtering out tables whose name don't match on the Oracle side.
  Per suggestion from @joaostorrer.
• Add options skip_tables, skip_views and skip_matviews to IMPORT FOREIGN SCHEMA.
  From a patch by @mkgrgis.

Release 2.6.0

Note: This release will fail to build with minor releases below 13.10, 14.7 and 15.2. Other major versions are not affected. oracle_fdw binaries built with later minor releases won't load with older PostgreSQL binaries, complaining about undefined symbols.
Keep the PostgreSQL server updated!

Enhancements:

• Skip Oracle savepoint management if the foreign server option isolation_level is set to read_only or if we are inside a read-only PostgreSQL transaction. This saves some round trips and avoids Oracle errors that might be thrown when entering a PL/pgSQL exception handler.
  Per suggestion from @math-g.

• Use explicit bulk fetching into arrays rather than OCI prefetching for Oracle SELECT statements. This greatly improves performance for LOB and LONG columns, since OCI prefetching didn't work if these data types.
  The limitation still applies for SDO_GEOMETRY columns. A new option lob_prefetch is used to configure how much of the LOBs is fetched with the rows to reduce the number of round trips.
  This enhancement was generously sponsored by Mipih (https://www.mipih.fr/).
  Thanks for @philflorent for help and testing!

Bugfixes:

• Inserting CLOBs with the server option nchar on can lead to the error

  ORA-24806: LOB form mismatch
  

  Report and patch by @jopoly.

• Don't push down LIMIT with FOR SHARE or FOR UPDATE. That caused the error

  ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
  

  Reported by @intgr.

• Fix BEFORE UPDATE triggers.
  If a BEFORE UPDATE trigger modified a column that was not updated, the changed column was not propagated to Oracle.
  Report and diagnosis by @jopoly.

• Don't free() strings in the environment.
  This could lead to crashes and unpredictable behavior.

• Conversions from TIMESTAMP WITH LOCAL TIME ZONE to timestamp without time zone were incorrect. To fix, set the Oracle session time zone from the PostgreSQL parameter timezone. Since not all time zones are accepted by all Oracle servers, this must be explicitly enabled by setting the set_timezone option on the foreign server.
  Thanks to @JamesInform for help with this.

• Make auto_explain work with oracle_fdw. This used to cause an error:

  oracleQueryPlan internal error: statement handle is not NULL
  

  Report by @Jaisus and @JamesInform. @JamesInform provided the essential clue that the problem is related to auto_explain.

• Fix "OCI-22061: invalid format text" errors when inserting some numbers with scientific notation into a foreign table.
  Report and analysis by @liubeck.

• Support whole-row references in RETURNING clauses.
  Report and patch by @jopoly.

• Make generated columns work correctly.
  This will fail to build with minor releases below 13.10, 14.7 and 15.2!
  Report and original patch by @jopoly.

Release 2.5.0

Many thanks to @michi-zuri for building Windows binaries!

Enhancements:

• Improve the Makefile so that the build process automatically detects Oracle Instant Client installations installed by packages on Linux.
  Patch by @chrullrich.

Bugfixes:

• Use the correct user mapping for views on foreign tables.
  This should use the mapping associated with the view owner.
  Reported by @JSilex.

• Fix a spurious error on UPDATE or DELETE if the column options key and"strip_zeros are used on the same column.
  Report and patch by @nori-shinoda.

• Fix “out of memory” and other errors on Linux systems with FIPS enabled.
  The cause is that calculating MD5 hashes on such systems causes errors.
  Reported by @HankDrews.

• Handle Oracle's TIMESTAMP WITH LOCAL TIME ZONE correctly.
  Oracle converts such values to its session time zone on output, but does not add a time zone offset, so PostgreSQL interpreted them incorrectly.
  Reported by @JamesInform.

Release 2.4.0

Enhancements:

• Support push-down of uuid values.
  Per request from @GaddipatiAsish.

• Introduce a server option nchar, turned off by default.
  This option, if set, enables the fix for NCHAR and NVARCHAR2 columns introduced in 2.3.0.
  We don't want that by default, since it causes a noticable performance hit and makes UPDATEs with long strings fail with ORA-01461.
  Problem reports by @threenotrump, @chunter and @philflorent.

• Push down LIMIT clauses if possible.
  These are pushed down as FETCH FIRST n ROWS ONLY from Oracle 12.2 on.
  Based on a patch by @darold, per request from @iliasaz.

---

Bugfixes:

• Fix a performance regression introduced in 2.3.0 by the fix for NCHAR and NVARCHAR2 columns.
  Reported by @philflorent.

• Fix a crash with type coerced array parameters.
  This can make queries fail with a WHERE condition like

  WHERE varcharcol = ANY ($1);
  

  where $1 is a text[].
  Reported by @samuelchoi16.

• Fix numeric precision in IMPORT FOREIGN SCHEMA.
  In Oracle the precision of a NUMBER can be less than the scale, but that is not allowed in PostgreSQL.
  That leads to errors during IMPORT.
  Reported by @alberto-dellera.

• Translate Oracle NUMBER to boolean correctly.
  The documentation says that numbers greater than 0 are mapped to TRUE, but oracle_fdw gagged on numbers greater than 1 with errors like

  ERROR:  invalid input syntax for type boolean: "2"
  

  Reported by @mariszin.

• Fix crash during ANALYZE of certain foreign tables.
  If the Oracle table has more columns than the foreign table, ANALYZE on the foreign table caused a crash.
  Reported by Jan and @SeanKimMel.

• Fix build with unset ORACLE_HOME and Instant Client 21.
  Reported by @fjf2002.

• Use the correct user mapping in SECURITY DEFINER contexts.
  Before this, oracle_fdw always used the current user to determine the security context, which is wrong if a foreign table was accessed in a SECURITY DEFINER function.
  Reported by @ksmalara.

Release 2.3.0

Upgrade note:

When upgrading from an older version of oracle_fdw, make sure to run

ALTER EXTENSION oracle_fdw UPDATE;

after installing the extension.

---

Enhancements:

• Support PostgreSQL v13. Support for 9.2 and 9.1 is dropped.

• Add a function oracle_execute to execute arbitrary SQL statements on Oracle. The statements must not return a result (e.g. DDL statements).

• Add an option dblink for Oracle database links.
  The option can be used on foreign tables or with IMPORT FOREIGN SCHEMA.
  Patch by @nboullis.

• Add a column option strip_zeros that automatically strips ASCII 0 characters from strings.
  Per request from @Sascha8a.

• New IMPORT FOREIGN SCHEMA options max_long, sample_percent and prefetch. These set the corresponding options on imported tables.
  Suggested by @ferfebles.

• Add support for isolation levels other than SERIALIZABLE.
  This is mostly because Oracle's implementation of SERIALIZABLE is so buggy.
  Patch by @ThinkJ001.

---

Bugfixes:

• Fix bad results with uncorrelated subqueries.
  This affects queries have a foreign scan with a filter based on a subplan.
  Reported by @srakazmus.

• Fix Internal oracle_fdw error: encountered unknown node type 144.
  This can happen in plans involving tables with identity columns.
  Reported by @todoubaba.

• Don't throw an error if DML statements modify no rows.
  This might be caused by a trigger on the Oracle side.
  Reported by @radist-nt.

• Fix NCHAR and NVARCHAR2 handling.
  With single-byte Oracle character sets, this bug led to replacement characters being used for characters not in the database character set.
  Reported by @srakazmus, and @chrullrich helped with the fix.

• Report a proper error for INSERT ... ON CONFLICT on partitioned tables.
  Report and fix by @ibarwick.

• Fix INSERT ... RETURNING if a table partition is a foreign table.
  It used to return NULL values by mistake.
  Report and analysis by @ibarwick.

Release 2.2.0

Enhancements:

• Add support for COPY to foreign tables (from PostgreSQL v11 on).
  This caused a crash before, as reported by @jkldv.

• Add a new collation option for IMPORT FOREIGN SCHEMA that controls case folding.
  Patch by @sahapasci.

• Add support for Oracle XMLTYPE.

• Set V$SESSION_CONNECT_INFO.CLIENT_DRIVER to oracle_fdw.
  This makes it easier to identify the session on the Oracle side.

Bugfixes:

• Fix crash or bad results with pushed down join queries.
  The query target list can change during query planning, but oracle_fdw relied on the one from before.
  This bug only manifests in PostgreSQL v11.
  Bug found by @Jaime2ndQuadrant and fixed by @yamatattsu.

• Fix push-down of foreign scan conditions with variables belonging to other tables.
  This can lead to wrong results and warnings like:

  WARNING:  column number x of foreign table "y" does not exist in foreign Oracle table, will be replaced by NULL
  

• Fix crash in UPDATE or DELETE where the optimizer can deduce that no scan is required, for example with WHERE 1 = 0.
  Reported by @mrship.

• Fix crash or bad results in queries with IN or = ANY where the element type on the right-hand side is different from the left-hand side type.

• Add support for reading infinite NUMBERs.
  Oracle NUMBER has infinite values, which are represented as ~ and -~.
  Since PostgreSQL's numeric does not know infinity, map these values to NaN in this case. For real and double precision we can use the normal infinity values.

• The readonly option of IMPORT FOREIGN SCHEMA didn't work properly:
  When set to false, it would still create read-only foreign tables.
  Reported by @jacobdr.

Release 2.1.0

Enhancements:

• Add support for the "json" PostgreSQL data type. On the Oracle side, CLOB or VARCHAR2 can be used.
  Suggested by @bsislow.

• Add support and documentation for building with Microsoft Visual Studio.
  Patch by @chrullrich.

• Enable delayed loading of the Oracle DLL on Windows. This allows better diagnostic messages if the library cannot be loaded.
  Patch by @chrullrich.

• Report the correct SQLSTATE for constraint violation and deadlock errors.
  Idea by "omistler".

• Push down outer joins to Oracle.
  Patch by @yamatattsu, courtesy of NTT OSS Center.

Bugfixes:

• Missing check if Oracle data types can be converted to PostgreSQL types.
  This bug was introduced in 2.0.0.

• When oracle_fdw is loaded, initializing background workers (e.g. for parallel query) failed with:

  ERROR:  invalid cache ID: 41
  

• Don't try to push down IS [NOT] DISTINCT FROM expressions. Oracle does not support this standard SQL syntax.

• Don't push down IS [NOT] NULL tests on boolean expressions. This caused

  ORA-00907: missing right parenthesis
  

  Noticed by @yamatattsu during testing with sqlsmith.

• Allow foreign tables to be defined on Oracle queries that contain double quotes. This caused an error message due to a sanity check that has become obsolete when this feature was introduced.
  Noted by @Bpapman.

• Disable push-down of foreign joins in queries with FOR UPDATE.
  These used to be pushed down before, but without the FOR UPDATE clause, leading to incorrect behavior in the face of concurrency.
  Noted and fixed by @yamatattsu.

Release 2.0.0

Incompatible changes:

• Remove the deprecated option plan_costs, since it is not very useful and is a maintenance burden.
  After an upgrade, tables with this option set should be updated with:

  ALTER FOREIGN TABLE ... OPTIONS (DROP plan_costs);
  

Enhancements:

• Push down 2-way inner joins in SELECT statements if all conditions can be pushed down.
  Patch by Tatsuro Yamada (@yamatattsu), courtesy of NTT OSS Center.

Bugfixes:

• oracle_fdw crashed on Windows if queries use a NULL parameter or an empty subselect.
  Report by PAscal Lemoy (@legrandlegrand).

• Reading srid.map sometimes caused errors even if everything was alright.
  Report by Paul Dziemiela (@pauldzy), analysis by Christian Ullrich (@chrullrich).

• Don't push down expressions with CLOB column references.
  Because of Oracle's inability to use CLOB in SQL expressions, this could lead to errors like

   ORA-00932: inconsistent datatypes: expected - got CLOB
  

• Fix bug in pg_terminate_backend handling.
  pg_terminate_backend (or SIGTERM) was not handled correctly, since neither was the Oracle query canceled nor did the backend terminate.
  Reported by Dmitry Chirkin (@josser).

Release 1.5.0

Enhancements:

• Add table option sample_percent to ANALYZE very large tables
  This can speed up ANALYZE significantly.
  Idea by @legrandlegrand.
• Introduce a table option prefetch for the Oracle row prefetch count
  This can speed up foreign table scans.
  Per discussion with by @legrandlegrand and @3kkani.
• Push down arbitrary IN and NOT IN expressions
  Up to now, only lists with constants were considered.
  Per request from @volkmarbuehringer.
• Push down ORDER BY expressions to Oracle from PostgreSQL 9.2 on
  Only expressions of numeric, date and timestamp datatypes are pushed down, since the string collations in PostgreSQL and Oracle cannot be guaranteed to be the same.
  Courtesy of NTT OSS Center, patch by @yamatattsu.

Bugfixes:

• Fix errors with now() and date/timestamp parameters in queries
  This bug was introduced in 1.3.0 and caused errors like
  ERROR: error executing query: OCIDateTimeFromText failed to convert parameter
  DETAIL: ORA-01843: not a valid month
  when now() or current_timestamp and friends or date/timestamp parameters were used.
  Report by Li Hailong.
• Fix errors when updating date/timestamp columns
  This bug was also introduced in 1.3.0 and caused errors like
  ORA-01856: BC/B.C. or AD/A.D. required
  if the Oracle and PostgreSQL types were different.
  Report by @sttachoires.
• IMPORT FOREIGN SCHEMA sometimes didn't import primary keys
  Report by @JMLessard, fix by @legrandlegrand.
• Fix memory leaks during ANALYZE
  This caused out of memory errors when large tables were analyzed.
  Report by @acheccuc.
• IMPORT FOREIGN SCHEMA should not tolerate missing remote schema
  It used to cause a warning, but the SQL standard requires an error.
  Report by @JMLessard.
• Fix crash when UPDATEing non-existent columns
  If a foreign table has more columns than the underlying Oracle table, these additional columns are treated as containing NULLs.
  However, oracle_fdw crashed on an attempt to change such a column with an UPDATE statement, which should not happen.
  Reported by @fnicollet.
• Fetch all columns from Oracle when the whole row is referenced
  This problem could lead to wrong NULL values in trigger functions or when the whole column is used in an expression.
  Reported by @fnicollet.

Release 1.4.0

Enhancements:

• Add option readonly for IMPORT FOREIGN TABLE that sets the foreign table option readonly to true on all imported foreign tables.
  Idea by @jgoux.
• Allow foreign tables based on arbitrary Oracle queries.
  The query, enclosed in parentheses, can be supplied as option table.
  Requested by @tsykes, @cirix81 and @jgoux.

Bugfixes:

• Selecting timestamps with lc_messages other than English caused errors like
  ORA-01406: la valeur de la colonne extraite a été tronquée
  Report by @dszczyt.
• Complicated queries could lead to an error like
  Internal oracle_fdw error: encountered unknown node type 524.
  Report by @bartonjd.
• PostGIS type geometry cannot be found if ArcGIS is installed.
  Report by @dtoller.