How to UPDATE/DELETE an Oracle Foreign Table with Foreign Data Wrapper from PostgreSQL?
3
votes
1
answer
2798
views
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
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
Asked by Rino
(229 rep)
Apr 3, 2020, 04:51 PM
Last activity: Apr 4, 2020, 02:54 AM
Last activity: Apr 4, 2020, 02:54 AM