ORA-08177 received using AWS RDS Read_only replica

[DaveFist]

Hi Laurenz / oracle_fdw team,

We're attempting to use oracle_fdw to connect to a read only replica Oracle 19c AWS RDS database and are receiving an ORA-08177. Connection / extraction on the primary is reported as being okay. I was wondering if using a read_only DB is supported for oracle_fdw?

We did find a possible fix for the issue (https://stackoverflow.com/questions/74605820/ora-08177-cant-serialize-access-for-the-transaction-with-oracle-fdw) which indicates setting the isolation_level might be a fix but we then hit https://github.com/laurenz/oracle_fdw/issues/420

Before I ask the team to upgrade their package I wanted to check that that it's even possible to use a read_only db (oracle RDS version 19c) or should we use the primary and move on? :-)

Best Regards, Dave

[DaveFist]

[DaveFist]

The following is generated at IMPORT FOREIGN SCHEMA

[laurenz]

If it is indeed a read-only database, no serialization error should happen. But then, Oracle's implementation of the "serializable" isolation level is so shoddy that anything is possible.

Do you get the error each time or only occasionally? Do you get the same error if you run this on Oracle:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT /* something */

If the Oracle database does no receive any modifications, it is safe to set isolation_level to read_committed. If that gets rid of your problem, great.

[DaveFist]

Many thanks for the reply...

Do you get the error each time or only occasionally? Every time we run it against the read_only instance.

I ran the following code as the RAPPO_RO user when connected to the read_only database in SQLDeveloper (and SQLPlus just to make sure SQLDeveloper wasn't doing something transactiony) and it worked okay...

_SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

select count() from cnss.ps_employees; select from cnss.ps_employees;

commit;_

Note, screenshot was generated with the "select " command removed as it extracts customer data.

Is there something else that could be causing the ORA-08177 when using the oracle_fdw command from Postgres?

Excellent point re: the isolation_level and updates. We have a busy Oracle system which is why we're trying to offload the schema extract to a read_only instance... it might be okay for reporting. I will check.

[laurenz]

Interesting.

Perhaps you can try to run the actual query in serializable isolation level:

SELECT col.table_name, col.column_name, col.data_type, col.data_type_owner,
       col.char_length, col.data_precision, col.data_scale, col.nullable,
       CASE WHEN primkey_col.position IS NOT NULL THEN 1 ELSE 0 END AS primary_key
FROM all_tab_columns%s col,
     (SELECT con.table_name, cons_col.column_name, cons_col.position
      FROM all_constraints%s con, all_cons_columns%s cons_col
      WHERE con.owner = cons_col.owner AND con.table_name = cons_col.table_name
        AND con.constraint_name = cons_col.constraint_name
        AND con.constraint_type = 'P' AND con.owner = :nsp) primkey_col
WHERE col.table_name = primkey_col.table_name(+) AND col.column_name = primkey_col.column_name(+)
  AND col.owner = :nsp
ORDER BY col.table_name, col.column_id;

You have to replace :nsp with the schema name in upper case and single quotes.

[DaveFist]

That SQL reproduces the error...

I'm new on this site and still trying to get admin access so I can run things as a privileged user, check logs etc. It does seem to confirm a problem or limitation on the target / Oracle side.

Do you know of anything specifically I should check?

[laurenz]

Great. That confirms that Oracle's broken implementation of SERIALIZABLE cannot run that query without getting a serialization error. My suspicion is that Oracle's guiding principle when implementing SERIALIZABLE was: since we are allowed to throw serialization errors, do that whenever the code that we'd have to write otherwise would be too complicated to get right.

Anyway, go ahead with repeatable_read.

I guess the problem is worked around, right?

By the way, for the future: it is better to use formatted text than screenshots. Formatted text can be found in a web search, which can help others.

[DaveFist]

Erracle. :-)

I will get the Postgres team to see about updating their client to the latest and go ahead with using repeatable_read. I have also noted your comment about formatted text verses images and shall do so in future.

Many thanks for your excellent help.