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.

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.