How to UPDATE/DELETE an Oracle Foreign Table with Foreign Data Wrapper from PostgreSQL?

Question

I need to transfer lots of row from my PostgreSQL database to a remote Oracle DB through the Oracle Foreign Data Wrapper (FDW). The required operation is so far INSERT to a Foreign Table (FT), but now am thinking to do DELETE/UPDATE as well. UPDATE is needed to flag the inserted rows based on some condition.

Originally I had a problem that INSERT to an Oracle FT got this error:

ORA-08177: can't serialize access for this transaction

And trying with different kind of Oracle Table showed the following pattern:

So then I choose the plain TABLE_4 (without any PK/index). But it turned out that DELETE/UPDATE does not like it, as stated in the error message:

ERROR:  no primary key column specified for foreign Oracle table
DETAIL:  For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
HINT:  Set the option "key" on the columns that belong to the primary key.

However when i use the TABLE_6 (with a PK), a DELETE/UPDATE command keeps on getting the same error that suggests to have a PK.

So how to UPDATE/DELETE an Oracle Foreign Table though Oracle FDW ?

The Oracle FDW docs here tells the following without any details/example that i need:

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.

and

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.

FYI, my playground:

• PostgreSQL 9.6.11 64-bit with PostGIS 2.5.1 
• PostgreSQL 9.6.15 64-bit with PostGIS 2.5.3 
• each Postgres is on top of Windows Server 2008 R2 Datacenter, using Oracle FDW version 1.1, connected to different Oracle DB both with version 11.2.0.4.0 64-bit.
• though PostGIS is vital in my PostgreSQL database, the data to be transfered to Oracle DB has no geometry at all

Answer 1

As the documentation and the error say, you have to define the foreign table with the key column option for all columns that belong to the primary key. Without knowing what the foreign key is, it is impossible to identify the row in the remote table that should be updated or deleted (for INSERT, that is not necessary).

Here is an example for a table definition that sets the key option:

CREATE FOREIGN TABLE atable (
   id    bigint OPTIONS (key 'true') NOT NULL,
   value text
) SERVER oraserver OPTIONS (table 'ATABLE');

In this example id is the primary key.

You can use the IMPORT FOREIGN SCHEMA statement to have oracle_fdw define foreign tables for you, which will automatically set the key option as appropriate.

---

The ORA-08177 is a different affair. It can be that oracle_fdw has to scan an Oracle table several times during a single statement (for example, if the foreign table scan in on the inner side of a nested loop join), and oracle_fdw has to ensure that the data seen during these scans are consistent.

That would not be guaranteed with the default READ COMMITTED transaction isolation level, so oracle_fdw has to use SERIALIZABLE (which is not serializable, but guarantees read stability).

Now Oracle has done a seriously bad job implementing this so-called SERIALIZABLE isolation level. It is not downright buggy, because according to the book it is always allowed to throw a serialization error in a serializable transaction, but Oracle hat interpreted this quite freely and throws a serialization error whenever implementing read stability correctly would have proven too cumbersome.

For example, if a concurrent INSERT causes an index page split, a serializable transaction that tries to INSERT at the same time will receive a serialization error. This is of course silly. Similarly, any data modification on a table that has received a data modification from a concurrent transaction will result in a serialization error, even if they don't touch the same rows.

Your response should be, like with all serialization errors, to retry the transaction and hope for more luck next time.

Because this is a common problem, and the cries for a different solution have become so loud, I have recently pulled a patch that enables you to set an option isolation_level on the foreign server. You can set this option to read_committed to change the isolation level to the unsafe value READ COMMITTED.

This is not in a released version yet, but you can safely use Git HEAD if you want to try it. Use this unsafe option at your own risk. For INSERTs, nothing can go wrong, but you are not safe from inconsistent query results in the presence of concurrent data modifications.

---

A word of caution: while bulk data modification works with oracle_fdw, it is not very efficient, because there is a round trip between PostgreSQL and Oracle for each affected row. The reason for this is in the foreign data wrapper API. It would be difficult to work around that, and I don't think bulk data modifications to be such an important use case.