Search code examples
postgresqloracle-fdw

Postgres - ORA-08177

I have a foreign table set up which points to a table in an Oracle DB. This very annoying error is being thrown:

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

I have done some reading and one of the suggestions is:

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

However, that does not seem to work:

zrec=# alter server ni00dev options ( set isolation_level 'read_committed ');
ERROR:  option "isolation_level" not found

Any suggestions on how to get rid of the ORA-08177?

Many thanks!

Solution

  • You can only SET an option to a value if you already had the option defined. Otherwise, you need to ADD it. Also, the option value must not contain an extra space character.

    ALTER SERVER ni00dev OPTIONS (ADD isolation_level 'read_committed');

    Note that you need at least oracle_fdw version 2.3.0 for this option. It is always recommended to run the latest version of oracle_fdw.