search

laurenz / oracle_fdw

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

Query using oracle_fdw being blocked? #696

Closed ericswiggum closed 3 months ago

ericswiggum commented 3 months ago

Hi @laurenz

Thank you for offering this extension, we have been using oracle_fdw for some time and we have a job that uses fdw to refresh materialized views that has now been failing for 2 weeks on one table in oracle with the below error. However when manually executed it completes successfully. I'm new to postgresql and my assumption is that there is something blocking this query. We did try adjusting the time when this job runs by an hour but that didn't make a difference. Am I correct to assume this query is being blocked in Oracle? Do you know of an easy remedy to an issue like this?

ERROR: 2024-07-22 05:30:03 INFO Refreshing materialized views 2024-07-22 05:30:03 INFO db::some_view1 2024-07-22 05:30:03 INFO db::some_view2 2024-07-22 05:30:03 INFO db::some_view3 2024-07-22 05:30:04 FATAL PG::FdwUnableToCreateReply: ERROR: error describing remote table: OCIStmtExecute failed to describe table DETAIL: ORA-00604: error occurred at recursive SQL level 1 ORA-08177: can't serialize access for this transaction ORA-02063: preceding 2 lines from some_dblink

laurenz commented 3 months ago

The cause is that Oracle didn't implement the SERIALIZABLE isolation level properly. It throws serialization errors on any pretext.

The solution is to reduce the transaction isolation level. That can impair the consistency of the results of certain complicated queries, but is in most practical cases a safe thing to do:

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

That should make the error disappear.

ericswiggum commented 3 months ago

The app owner decided to change the timing of the job execution. After which it appears to be running and completing successfully. Thank you for reaching out, I'll mark this closed.