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>