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
keyoption is set on all columns that belong to the table's primary key. Failure to do so will result in errors.
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 126.96.36.199.0 64-bit.
- though PostGIS is vital in my PostgreSQL database, the data to be transfered to Oracle DB has no geometry at all