The annoying case of ORA-08177 solved

We had setup a foreign data wrapper from postgres to oracle using the oracle_fdw

https://www.postgresql.fastware.com/postgresql-insider-fdw-ora-bas

But, about 50% of the time it would throw a ORA-08177: can’t serialize access for the transaction

Argh… why is it being so buggy. Well, reading here

https://github.com/laurenz/oracle_fdw

If you don’t specify it isolation_level (optional, defaults to serializable)

BUT, Unfortunately Oracle’s implementation of SERIALIZABLE is rather bad and causes serialization errors (ORA-08177) in unexpected situations…

To fix it,

alter server oracle options ( set isolation_level 'read_committed ');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s