1

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:

enter image description here

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

5
  • Yaay! @Laurenz himself. All right, I get it now. I did not realize that IMPORT FOREIGN SCHEMA automatically recognizes KEY(s) of table in the remote DB. The reason my FTs did not catch the KEY was because I always re-used (copy-paste-rename-run) the FT DDL from my first DDL that reads a table in remote DB, and this table has no primary key. So all my following 5 or 6 FTs did not recognize KEY even their dedicated remote table has KEY. Now with KEY defined in FT DDL, I can UPDATE and DELETE as expected. And by the way the 2nd sentence above is to avoid the term FOREIGN KEY :)
    – Rino
    Apr 5 '20 at 4:00
  • My observations show INSERT is smoothly done to 2 kinds of remote table in Oracle: (1) with no PK and no index, and (2) no Index, with PK, and with ROWDEPENDENCIES option. Otherwise the ORA-08177 strikes and if I retry to Insert again several time (like you said) it will eventually, luckily done. My question: could you please explain the above (the 2 kinds of remote table) ? Or is it wrong conclusion or perhaps version-specific ?
    – Rino
    Apr 5 '20 at 4:02
  • What do you mean by "two kinds of remote table"? I must have been unclear. Apr 5 '20 at 7:47
  • Two kinds of table : (1) no PK & no index; (2) with PK, with ROWDEPENDENCIES option, no index. To these tables my Insert was smooth and never fail. They both are consecutively TABLE_2 and TABLE_6 in my orange matrix in my question. Other than the 2 kinds, the ORA-08177 strikes. So I asked if this is wrong conclusion or perhaps version-specific?
    – Rino
    Apr 5 '20 at 8:38
  • On a table with no index it doesn't surprise me, but you don't want such tables. What is ROWDEPENDENCIES? Apr 5 '20 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.