1

I created an updatable view V1 in an Oracle 11g DB (it has INSTEAD OF triggers for UPDATE, DELETE and INSERT); it originally had a GROUP BY query in it, which I moved to another view V2. This other view is joined in my view V1, making the view V1 updatable from Oracle.

I create a foreign table in a PostgreSQL DB from the Oracle view V1 using oracle_fdw; OPTIONS (key 'true') is set for the primary key column.

I expect the foreign table to be updatable, but I receive this error:

Error synchronizing data with database

Reason: SQL-Fehler [HV00L]: ERROR: error executing query: OCIStmtExecute failed to execute remote query Detail: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
from view with DISTINCT, GROUP BY, etc.

Is there a way I can tell oracle_fdw to allow updating the foreign table? As mentioned, UPDATE on the view V1 works fine from Oracle side.

SELECT ... FOR UPDATE (from the Oracle side) fails with the same error ORA-02014.

I guess it comes down to preventing oracle_fdw to do a SELECT FOR UPDATE on the view when UPDATEing the foreign table, but the documentation seems to hint at this row-by-row processing being by design of foreign data wrappers.

So, is there a way? Or do I have to place my INSTEAD OF triggers on a view of the PostgreSQL side and only map Oracle base tables as foreign tables?

Improve this question

1 Answer 1

Reset to default
1

No, that is not possible. As you correctly mention, the UPDATE happens in two parts: first, the primary key column is fetched with SELECT ... FOR UPDATE, then each rown that needs to change it updated individually.

It is theoretically possible to avoid that by performing the update in the first (scan) phase and doing nothing in the second phase, but that is not trivial, and there are no plans to implement that.

Improve this answer
2
  • 1
    I avoided the issue by using two Oracle views of the same shape, one for SELECT, one for UPDATE, INSERT, DELETE; both will be exposed as foreign tables via oracle_fdw to PostgrSQL. The former will include the full query including the GROUP BY, minus the INSTEAD OF triggers, the latter will just NULL all columns of grouped entities and just fill their key columns via subqueries; this "write" view will then get used by the INSTEAD OF triggers and works fine with SELECT ... FOR UPDATE. I use both in a postgres view that will query "read" foreign table and use "write" foreign table in triggers.
    – ENOTTY
    Commented Jul 25, 2023 at 9:17
  • Ah, smart. Thanks for sharing the idea.
    – Laurenz Albe
    Commented Jul 25, 2023 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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