search

laurenz / oracle_fdw

PostgreSQL Foreign Data Wrapper for Oracle
http://laurenz.github.io/oracle_fdw/
Other
496 stars 156 forks source link

Help With ORA-08177: can't serialize access for this transaction #664

Closed LandonStatis closed 7 months ago

LandonStatis commented 7 months ago

I've been reading a lot on this error that Postgres throws with foreign tables. Does anyone have the answer?

ORA-08177: can't serialize access for this transaction

Some helpful info:

postgres=# SELECT oracle_diag('oracle_dev'); oracle_diag

oracle_fdw 2.6.0, PostgreSQL 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), Oracle client 21.13.0.0.0, Oracle server 12.2.0.1.0

Insert statement is pretty basic. It's within a cursor loop:

INSERT INTO daily_recommendation (ticker, current_recommendation, recommendation_text, prior_recommendation, prior_recommendation_text, date_appended) VALUES (v_ticker, v_rec, v_rec_text, v_prior_rec, v_prior_rec_text, CURRENT_DATE)

Many thanks!

laurenz commented 7 months ago

This is a duplicate of #577.

The only viable solution is to use READ COMITTED isolation on the Oracle side, and the down side of that is that some complicated queries on the foreign table could potentially receive inconsistent results.

ALTER SERVER oracle_dev OPTIONS (ADD isolation_level 'repeatable_read');
LandonStatis commented 7 months ago

That did it.......many thanks!!

laurenz commented 7 months ago

You are welcome.