ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

[chrisbitmead]

I've setup the postgres foreign wrapper, including specifying key fields. I am able to select from those tables. But if I try and delete...

delete from my_user_ora;
ERROR:  error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

A lot of googling and I haven't really found anybody else with this problem. The only thing I found was issue #700 where somebody says they got this error on a regression test, and the maintainers here said don't worry about the regression test. Unless I'm missing something, it seems to be a real issue, and a fatal one.

We are using postgres 15.2 and oracle 19c. The reason I believe we are using those versions is that is what Amazon web services are able to provide for us. Since the regression test mentioned above failed on Postgres 16 and Oracle 23, it doesn't seem like attempting an upgrade will get me anywhere.

[laurenz]

Either the Oracle database doesn't allow you to write, or you set the foreign server option isolation_level to read_only.

[chrisbitmead]

OK.... how about you document that, because "read only" has no conventional meaning in the context of isolation levels, it's not one of the standard isolation levels either in postgresql or ANSI.

[laurenz]

The documentation has

• isolation_level (optional, defaults to serializable)

  The transaction isolation level to use at the Oracle database.
  The value can be serializable, read_committed or read_only.

Is there anything ambiguous about that?
Sure, that sentence assumes that you are familiar with Oracle's isolation levels, but I don't think it is my task to document Oracle.
Oracle has never been great about following the standard, particularly in the context of transaction isolation. But READ ONLY is standard SQL, see chapter 17.3 <transaction characteristics>:

<transaction characteristics> ::=
      [ <transaction mode> [ { <comma> <transaction mode> }... ] ]

<transaction mode> ::=
     <isolation level>
   | <transaction access mode>
   | <diagnostics size>

<transaction access mode> ::=
    READ ONLY
  | READ WRITE

<isolation level> ::=
  ISOLATION LEVEL <level of isolation>

<level of isolation> ::=
     READ UNCOMMITTED
   | READ COMMITTED
   | REPEATABLE READ
   | SERIALIZABLE

<diagnostics size> ::=
   DIAGNOSTICS SIZE <number of conditions>

<number of conditions> ::=
   <simple value specification>

[philflorent]

Hi,
A good documentation/readme has to be concise. Here there might be a confusion between isolation level and access mode but it's useless to document that no DML can be performed in a read only transaction, no matter the level. "Read only" is self descriptive.
The main problem with oracle_fdw and ACID is in fact that Oracle serializable level is buggy, in read/write access mode, but nothing can be done by oracle_fdw itself.
Imo with oracle_fdw it is convenient to use :

• serializable read only for select
• read committed read write for DML
  If the queries are very simple, read committed is OK for select though.
  Best regards,
  Phil